Mini Scheduler in Excel

[chullan88]

Hi excel gurus,

Im trying to build a small scheduler in Excel.
I have a list of activities which are related to each other by any of 3 relationships:

FS - successor start only after predecessor finish
SS - successor start at the same time as predecessor
FF - both successor and predecessor finish at the same date

I know this can be done using MS Project but Im trying to achieve something simpler and in Excel as its more flexible.

Ive attached sheet with the desired results

Thanks in advance

[dflak]

I think this is what you want. I copied the Workday function all the way down so I get slightly different results.

The macro name is MakeSchedule.

[chullan88]

Hi dflak,

Thanks a lot for taking the time...

I found some glitches while checkingthe macro.I think the finsh date also needs to be programmed( if possible)

Also if you could add some error handling like if all relationships are not entered dont run the macro and display a error message.

Please find attached the file which I found some minor errors in the finish date where all C,D and E finish date should be the same

[dflak]

The answer is to work the Workday.Intl formula backwards. -- I'll need to work that one out.

[dflak]

Rather than figure out something sophisticated to reverse engineer the formula, I decided to use brute force. For FF, I set the start date to one day prior to the desired finish date. Then I let it run through the formula and compare it to the desired finish date, if they are not equal, I take another day off the start date and loop until the start date, run through the formula is equal to the finish date.

[chullan88]

Hi dflak...

Even though I didn't understand what you did there,it works perfectly...

Just a couple of queries ( Maybe Im getting greedy here)

1. Is it possible to have multiple relationships for a activity ( and the latest date result from these relationships will be used .For Eg if B has FS from A and FS from C also the latest finish date of these two will be assigned to B finish date.
2. Error handling if all relationships are not entered.


thanks in advance

[dflak]

It may be possible, but I'm not taking it on. If you want that much, get MS-Project.