# Calculating a future date taking holidays into account

Occasional Contributor

# Calculating a future date taking holidays into account

Hello folk's,

I'm trying to calculate a 2 month future date that excludes holidays and weekends.  I have one formula that calculates 10 working days, which is shown below and works perfectly well.

My manually entered start date is entered in cell H10.

The 10th working day is in cell H12 (=IF(H10="","",Workday(H10,R3,Q7:Q45))  The cell reference R3 holds the number 10, (the number of working days given to respond).  Reference to Q7:Q45 is my list of holidays to be taken into account to enable 10 working days, so that if the returned date falls on a weekend or holiday, the next working day will be calculated.

The 2 month advance Date is currently holding the formula - (=IF(H10="","",WORKDAY(WORKDAY(EDATE(H10,2),-1),1))  - This formula takes weekends into account so if the date returned based on the start date falls on a weekend, then the formula will calculate the next Monday date.  What this formula does not do is take holidays into account in the same way the first 10 working days formula does.

Any help to figure out the formula I need to work out a 2 month response date taking weekends and holidays into account will be very warmly welcomed indeed.

DickScrongle

2 Replies

# Re: Calculating a future date taking holidays into account

=IF(H10="","",WORKDAY(EDATE(H10,2)-1,1,Q7:Q45))

# Re: Calculating a future date taking holidays into account

@Hans Vogelaar Hans, huge thanks, this works brilliantly.  Thank you so much!