Automatic Scheduler

**Nuggetross** · 02-21-2013, 05:58 PM

Hey all,

I've been working on an automatic scheduler in Excel off and on, and I just can't seem to get it to work. I end up doing a ridiculous number of nested ifs. I'm hoping you all could help.

So, a project is assigned on a specific date. That project also has a release date down the road. Here are some of the rules for the scheduler:

Note: Tasks are done once a week. Projects can be released any day of the week.

1. Once assigned, a project is "Built" that week (a week being Sunday-Sunday). That project is also updated the next week.
2. Projects are updated once every 4 weeks, unless the Sunday of this week is within 8 weeks of the release date.
-if within 8 weeks, the project is updated every week
3. Projects are updated until 2 weeks after release
4. If a new "asset" is released during this schedule, an update is required during the release of that update and the following week. After that, it resumes the ><8 week schedule.

Other things to note:
-release dates change and are sometimes not available

Basically, I'd like to have the layout be similar to a calendar. I've attached a sample worksheet. The new asset and release dates should be manually entered.

In the sample, these are the label definitions:

BLD=build
1UP=first update
WUP=weekly update (<=8 weeks release)
MUP=monthly update (>8 weeks release)
FUP=final update (2 weeks past release)
NEW=new asset
NUP=new asset update

Please let me know if you have any questions.

**Nuggetross** · 02-22-2013, 03:34 PM

Any suggestions on how to proceed would be great. If I wasn't clear enough, I couldn't even get my nested ifs to work correctly.

**Jacc** · 02-23-2013, 05:43 AM

This is from cell J2, the first in the range with if formulas.

Formula:

Please Login or Register  to view this content.

This is from cell AT2, so over the columns the previous formula grows to this.

Formula:

Please Login or Register  to view this content.

I never seen such massive if formulas and I'm not sure how to help. My first thought is that you need another software, Microsoft Project or something, but that's not what you came here to hear.
I find it helpful to align the if's like this, you can do that in the cells as well using Alt + Enter. I also started commenting on each IF as you can see in the top formula.
The criteria you list seems reasonable but the formula still ends up with 28 if's, I'm sure some things could be simplified or eliminated.

Here's another thing I tried right now. I copied the content of cell AT2 into Word. I split it up with Enter as before. The cool thing with Word is that you can press Alt +Shift and then use Arrow Up and Arrow Down to move the rows around. It was then easy to sort them by output. A lot easier to get an overview.

Formula:

Please Login or Register  to view this content.

That's all I can help you with now.
Right now my time is limited and over the next week or two so I'll be travelling so I really hope more people will contribute. If not I'll give it a try when I come back.

**Nuggetross** · 02-24-2013, 12:23 PM

thanks for the input.

i was hoping to get one set of formulas work in all columns. but anything that works with no errors would be great. i couldn't believe that so few rules could get so complicated. i'll see how this works out.