Network days

[Morfi]

Hi,

I am trying to figure out how to calculate a deadline date taking into account bank holidays and weekends.

If I receive something on a particular date I have 15 working days to respond to it. I have the following columns:

A - this column lists bank holidays from row 2 to row 10
B - date of receipt of correspondence
C - date that correspondence must be responded to by - i want this column to calculate the latest day of response i.e. 15 working days after the date of receipt.

What would be the formula I should use to add 15 WORKING days on to the date of receipt? I know it has to include NETWORK DAYS but I can't quite get it right.

Thanks

 

[Analyze_This]

Network days tells you the difference between two dates (excluding the number of holidays). If you have your start date and the number of holidays, a row in column C would be =B2+SUM(A2:A10)+15.

 

[Jonmo1]

Try using WorkDay

=WORKDAY(A1,15,B1:B10)

A1 = Start Date.
B1:B10 = list of holidays.

Hope that helps.

 

[Morfi]

Thanks for your help with this.

The first reply doesn't seem to be working. If I put in a date of receipt as 01/04/2011 it's giving me a deadline date of 01/12/2050.

The second reply is just coming up with #VALUE

 

[Jonmo1]

Post the actual formula you used...

Based on your original post, it should probably be

=WORKDAY(B2,15,A2:A10)

 

[Analyze_This]

Are you actually listing the date of the holidays? If so, it will wildly inflate the numbers (excel sees dates as numbers, so 1/1/11 would be 40544). Try just listing the number of holidays instead of their dates.

 

[Morfi]

I've got it working now. Thanks for your help!