Account for holidays in an automated calendar

I am trying to create a master time and action calendar. I want to plug in the end date and have all the dates that each stage needs to be completed by to populate based on the amount of days needed in the column next to it. I have worked this up easily enough however now I need to account for dates that my facility is closed... not US holidays. Trying to figure out a formula to add x amount of days to the date before it but if it falls on a holiday then go to the next usable date.

It would help if we could see what you've got so far - please attach a sample workbook.

Pete

Time and Action Calendar.xlsx

Attached, Tab one is the data and tab two are the dates that the facility is closed

The date entered in D16 of the data sheet has actually been taken as 2nd January 2014, and you are working backwards from this, whereas your holiday dates start in December 2014. Should your date be 2nd January 2015 or even 2nd January 2016 ? To show this accurately you need to ensure that your list of holiday dates starts well before the anticipated starting date (of the project). What are the dates for Easter this year (presuming that is what the block in April of next year actually represents)?

Also, are you open at weekends, or do you want the formula to take account of these and disregard them?

Hope this helps.

Pete

Hi Pete,

We are open weekends so the dates include that. The entry date on the bold box changes so there will be times when there are no holidays that affect it and there will be times when it fall right on a holiday date. you can change the date in the entry box on the data page to reflect this. I have updated on my end for the entry date to read 4/14/2015 for the sake of getting something to calculate.

Elizabeth

Hi Elizabeth,

see attached file. Note that I have set up your table of holiday dates in a different way, with start and end dates, rather than listing them all individually. I've also formatted all dates so that you can see the year.

Hope this helps.

Pete

Perfect thank you!