Dates with Holidays question

[Guest]

I am trying to set up a date schedule but am not sure of the best way to
incorporate holidays. For example, my start date is 12/25/05. I need to add
a formula that takes 12/25/05 plus 90 days but if that date falls on a
Saturday or Sunday, the returned date needs to be Monday. The end result
would be 3/27/06. But, I also need to say that if the end date (3/27/06 in
my example) falls on a holiday, I need to go to the next date but again if
that date falls on a Saturday, need to go to Monday.

I hope this makes sense. Any suggestions would be greatly appreciated.

 

[Guest]

Hi HJ,
One solution is to use the WORKDAY function. You'll need to have installed
the Analysis ToolPak, if not installed. Go to Tools > Add-ins... check
Analysis ToolPak and then click OK. This action will install among other
things the WORKDAY function.
Then, with a date in cell A1, put this code in A2:

=WORKDAY(A1+90,1,G1:G10)

This formula says to take the date in A1 and add 90 days to it. Then take
the next workday by looking past Saturdays and Sundays. The "1" in the
formula refers to the next workday. The last range entry, G1:G10, is just an
example and refers to a group of cells in which you enter the holidays that
you wish to use. If you want to use eight holidays, enter those dates in a
group of unused cells and refer to that range in your formula.

Regards,