Creating schedule exclude weekends

[mmohawk]

I'm trying to create a schedule that will calculate dates but I don't want it to include Sundays or holidays. Can I create an exclude list? I want to be able to enter a start date of my project and then it would calculate the dates according to the amount of time for each step.

Thanks, Mic

 

[PeterMoran]

Hi,

Try this for starters:
Define a vertical range "Holidays" and include several holiday dates in ascending order.
Enter your start date in cell A2.
The basic formula for entering workdays (Mon-Fri) below A2 would be:
=IF(WEEKDAY(A2+1)=7,A2+3,A2+1)
You can checkout WEEKDAY in Excel Help

To accomodate the holidays this would need to be expanded with a vlookup to the following in cell A3 and then copied down:
=IF(ISNA(VLOOKUP(IF(WEEKDAY(A2+1)=7,A2+3,A2+1),Holidays,1,FALSE)),IF(WEEKDAY(A2+1)=7,A2+3,A2+1),IF(WEEKDAY(A2+1)=7,A2+3,A2+1)+1)

This is limited to not having consecutive workdays as holidays in the holiday list, otherwise it becomes further complicated.

See how this looks and lets know if you need further help.

Good Luck!
Peter Moran

 

[mmohawk]

Would you be able to send me an excel document showing this? I'm not quite 100% sure how to put this into my design. m_mohawk@hotmail.com Thank you for all your help.

Thanks, Mic