Calendar days (not working days) calculation, date falls on weekend, move to next business day?

[scottiesq]

I am able to calculate dates in advanced based on a number of days ahead. However, I need the formula to move the given date to the next business day if it falls on a weekend or holiday.

Thus, it is a kind of a hybrid formula, Calendar date plus certain amount of days (e.g. 5 days) but falls on a Saturday, so need it moved to the next Monday. I am familiar with the holidays date range feature, but do not know how to make thes last part work (meaning moving to the next business day).

Thank you for your help.

 

[AdonisBlue]

Hi Barry. I've been working with the formula you have given but it goes one year back.

What I need to do: I have one date ( H10) and need to add 45 days to this date in the next Column I
the answer must include the following requirements
1: If there are not dates Column I must be blank
2: If the 45th day falls on a weekend the calculation must move to the next workday which is Monday

Does that explain things. I will appreciate your help. Thank you

 

[barry houdini]

I've been working with the formula you have given but it goes one year back.

I'm not sure what you mean, can you give a specific example?

I think my suggested formula should do exactly what you asked:

=IF(H10="";"";WORKDAY(H10+45-1;1))

If H10 is blank the formula will return a blank. If H10 is 31 March 2015, for example, then the formula will return that date + 45 days, which is Friday 15 May 2015. If H10 is a day later, 1 April 2015 then 45 days from that date is Saturday 16 May 2015....so the formula returns the next working day as required, Monday 18 May 2015.

 

[AdonisBlue]

Hi Barry

My apolgies for only responding now.

Thank you it worked.

I'm not sure what you mean, can you give a specific example?

I think my suggested formula should do exactly what you asked:



If H10 is blank the formula will return a blank. If H10 is 31 March 2015, for example, then the formula will return that date + 45 days, which is Friday 15 May 2015. If H10 is a day later, 1 April 2015 then 45 days from that date is Saturday 16 May 2015....so the formula returns the next working day as required, Monday 18 May 2015.

 

[Kbeecher]

My issue is similar to the above; however, I am getting stuck. I am trying to ultimately determine if a response was provided in 30 calendar days or less. So I have a receipt date and a response date, I subtract response date from receipt date and say if result is > 30, then "untimely". If my calculation yields for instance that it took 31 or say 32 days to respond, I don't want to count this as an error in testing if they responded on day 31 or on day 32 because the 30th day fell on a weekend or federal holiday. I am not sure how to approach this.

 

[mole999]

My issue is similar to the above; however, I am getting stuck. I am trying to ultimately determine if a response was provided in 30 calendar days or less. So I have a receipt date and a response date, I subtract response date from receipt date and say if result is > 30, then "untimely". If my calculation yields for instance that it took 31 or say 32 days to respond, I don't want to count this as an error in testing if they responded on day 31 or on day 32 because the 30th day fell on a weekend or federal holiday. I am not sure how to approach this.

please start a new thread