Project Scheduling, Include Weekends, Exclude Holidays...Arghhh...

[mlib27]

Hi...thank you all for what you do here...tremendous help to the rest of us! Anyway, I'm trying to create a project schedule for the renovation of certain parts of a building. The workdays are slated to be Saturdays, Sundays, Mondays, & Tuesdays only. However, on occasion there's a holiday on a Sat, Sun, Mon, or Tues. The information I have is a start date and an estimated number of days required for each renovation area.

So...clearly the workday function is out...looking at my current worksheet, I'm setup as the following...

D20: Quantity of work days required
E20: Start Date (03/19/2011)
F20: Finish Date (this would be start date + D20)

J19:J173: this is a list of all dates that are not work dates, mostly Wednesdays, Thursdays, Fridays, but it also includes some other days such as Mondays that are holidays, etc.

So...what I really really need is a formula for F20 that calculates the end date, but knows that the stuff in J19:J173 are not available as work dates...any ideas????

Thank you!

 

[mlib27]

I'm running Excel 2007, so I don't have the workday.intl function...

 

[barry houdini]

I'm running Excel 2007, so I don't have the workday.intl function...

Without that option it's a little more complex. Try this array formula in F20

=SMALL(IF(WEEKDAY(ROW(INDIRECT("1:"&D20*10))+E20+1)<5,IF(COUNTIF(J$19:J$173,ROW(INDIRECT("1:"&D20*10))+E20)=0,ROW(INDIRECT("1:"&D20*10)))),D20)+E20

confirmed with CTRL+SHIFT+ENTER

 

[mlib27]

Thanks Barry....looks pretty close! I'm wondering if we need to do something to your formula so that it counts the first day as a work day...

For example, my starting day for the project is 03/19/2011, and the 1st renovation takes 4 days, which should yield a completion date of 03/22/2011, while it's currently saying 03/26/2011...

Deeply appreciate your help!

Best,

Matt

 

[barry houdini]

Hello Matt,

Yes, the formula I suggested works like WORKDAY function in that it doesn't count the start date...but you can change it to do so, try this version

=SMALL(IF(WEEKDAY(ROW(INDIRECT("1:"&D20*10))+E20)<5,IF(COUNTIF(J$19:J$173,ROW(INDIRECT("1:"&D20*10))+E20-1)=0,ROW(INDIRECT("1:"&D20*10)))),D20)+E20-1

I tested in Excel 2010 and that formula gives the same results as this one

=WORKDAY.INTL(E20-1,D20,"0011100",J$19:J$173)

 

[mlib27]

Thanks Barry! They should rename this site "Excel Gods."

You're formula works perfectly, greatly appreciated!

I was hoping I could trouble you to take this one step further, and I'm pretty sure I'll be set...

With D20 being the quantity of days needed to complete a renovation area, for example 4 days for Floor 9, 8 days for Floor 8, etc...

And E20 being the very first start date of the entire project...03/19/2011...

And F20 being your formula that calcs the end date for the renovation area, recognizing the group of days in J20:J173 that are not available for working...

The next steps is to calc the start date for the next renovation area, whicy would be in E21...and the idea is for E21 to find the next available work day based on the result in F20, but also while recognizing the days that can't be used in J20:J173...

Ultimately...looking for a way to automate the next start date in E21, E22, E23, etc., etc.

Thoughts?

Thanks again for your help! You guys are the best!

 

[barry houdini]

You can use a simpler version of the previous formula, i.e. in E21 copied down

=SMALL(IF(WEEKDAY({1,2,3,4,5,6,7}+F20+1)<5,IF(COUNTIF(J$19:J$173,{1,2,3,4,5,6,7}+F20)=0,{1,2,3,4,5,6,7})),1)+F20

by using {1,2,3,4,5,6,7} that will work OK as long as the next workday will always be one of the next 7 days, so if you might have any holiday blocks you might want to increase that.......

 

[mlib27]

Thanks Barry...can I just carry it out to 20 by doing 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 ??? The project is at a university and we do have a 19ish-day vacation block in December/January...

Just want to make sure I can run the formula by simply adding those extra days right into your formula in the 3 areas where you currently have it going to 7.

Thanks again...unparalleled help!

 

[malik0364]

{moderator removed - hijacking thread}

Malik, please raise your own thread rather than tagging onto an existing one.

 

[barry houdini]

The project is at a university and we do have a 19ish-day vacation block in December/January...

OK, that could have an impact on the first formula because where I have D20*10 if D20 is a low value like 1 or 2 then that will only look 10 or 20 days ahead for a working day, so you might want to make the 3 instances of D20*10 into D20*25 or similar.

Then for the E21 version I would revert to a similar setup to the first one, i.e. in E21 confirmed with CTRL+SHIFT+ENTER and copied down

=SMALL(IF(WEEKDAY(ROW(INDIRECT("1:25"))+F20+1)<5,IF(COUNTIF(J$19:J$173,ROW(INDIRECT("1:25"))+F20)=0,ROW(INDIRECT("1:25")))),1)+F20