Project End Date calculator

[dmerrick]

I'm trying to build a spreadsheet out that allows me to calculate and adjust the end date of a project based on Time-Off. I have a simply calculator that works for M-F 8 hour workdays.
=WORKDAY((B1+ROUND((B9/8), 0)), (B2/8), B13:B32)
B1=Start date
B2= project length in hours
B9=Time Off taken
B13:B32=holidays

Sample:
start date: 1/1/2011
Length: 960 hours
Time-Off: 0
holidays: 2/14/2011
End date= 6/20/2011

What I want to be able to do is be able to change three variables:
length of workday - customer may request 10 hour day vs. 8 hour day
length of work week - say 4 day or 6 day week vs. 5 day
workdays - Tue-Sat or Fri-Mon vs. Mon-Fri

I've looked at some of the existing UDFs available and while I can find ones that allow me to include/exclude particular days and modify hours all of them seem to be based on networkdays and require an end date which is the information I'm trying to determine.

Any help is appreciated. If someone can point me in the right direction and/or provide the how the solution works as I prefer to use solutions I understand, mainly so I can modify as needed in the future.

Thanks

 

[dmerrick]

Hmm
I think I may have answered the work day length part. I think I can just divide the Project length and Time-Off numbers by whatever my day length is.
so 960/10=96 days vs. 960/8=120.

Is there any where to find out exactly how workday functions? That would make it easier to figure out work-arounds.

I also found a way to modify the WORKDAY function input to move the weekend days. But I still need to find a way to change length of week.

 

[dmerrick]

Finally got the issues figured out.

The final formula was : =WORKDAY2((B1+ROUND((B9/B3), 0))-1, (B2/B3), Hours!I9, holidays)
Notice the function is WORKDAY2 which is from http://www.cpearson.com/Excel/BetterWorkday.aspx

Works like a charm

Hours!I9 is a series of IF statements that checks which days are marked as non-Work days and calculates the total for the WORKDAY2 function.

(B1+ROUND((B9/B3), 0))-1 is used to add in Time-Off and holiday hours to the start date.

The only remaining bug now is that my formula give me the last FULL WorkDay date vs. the actual last day. So if I have 16 hours left on the contract it would show me a Tuesday as my last day, but if I have 17 hours it still shows Tuesday even though the actual last day would be Wednesday. A small thing but I'd still like to get it fixed.

Thanks