Deadline calculation including weekends and holidays but if date result falls on weekend or holiday the next business day is displayed

[Katidyd]

Hello folks,

I am new to Excel and new to this forum. I am using Excel 2010.

I am trying to create a court filing deadline calculator that calculates two different kinds of deadlines. One for business days and one for what I call calendar days.

I have been able to figure out how to calculate "business days" deadlines to exclude weekends and holidays with the WORKDAY formula for the first deadline category.

I need to be able to calculate the deadlines for the second category, for the following example:

A document was filed with the court and served on an opposing party on March 1, 2019. The opposing party has 30 days (including weekends and holidays) to file their reply. However, if the 30th day deadline falls on a weekend or holiday, the date result will automatically advance to the next business day. March 1, 2019 would not be included in the calculation - the 30 day time clock starts on March 2. Would anyone be able to assist with this?

Many thanks!

Kate

 

[Dave Patton]

Excel 2010
	B	C	D
1	Served	Days
2	Fri Mar 01, 2019	30	Mon Apr 01, 2019
3
2a

Cell Formulas
Range	Formula
D2	=WORKDAY(B2+C2-1,1)

 

[Katidyd]

Thank you very much Mr. Paton. I appreciate your time and trouble.

I assume that I would also have to define a range of dates that are Canadian holidays in British Columbia and add that into the formula.

=WORKDAY(C8+A13+1,1,Holidays)

I note that your formula uses -1. I used +1 thinking I am adding an extra day to the 30 days so the service day is not counted. If the -1 represents something else in your formula, please let me know.

Hello folks,

I am new to Excel and new to this forum. I am using Excel 2010.

I am trying to create a court filing deadline calculator that calculates two different kinds of deadlines. One for business days and one for what I call calendar days.

I have been able to figure out how to calculate "business days" deadlines to exclude weekends and holidays with the WORKDAY formula for the first deadline category.

I need to be able to calculate the deadlines for the second category, for the following example:

A document was filed with the court and served on an opposing party on March 1, 2019. The opposing party has 30 days (including weekends and holidays) to file their reply. However, if the 30th day deadline falls on a weekend or holiday, the date result will automatically advance to the next business day. March 1, 2019 would not be included in the calculation - the 30 day time clock starts on March 2. Would anyone be able to assist with this?

Many thanks!

Kate

 

[Dave Patton]

Check the results to ensure that they are what you require.

I used 30-1 since the second parameter ie 1 adds 1 day.

Yes, include your range of Holidays

This may be a duplicate post; I do not see my follow-up post.

 

[Katidyd]

Thanks very much. And, my apologies for misspelling your last name in my original response.

 

[Katidyd]

Good evening,

I appreciate the assistance.

The formula mentioned in this thread, when I add a range of holidays, counts any holiday that falls in the calculated deadline range, adding adds an extra day to account for it.

I wish the spreadsheet/formula to only "recognize" a holiday when the deadline date result falls on a holiday, and should then choose the next business day.

It may be that there is not a simple formula for that, and there may be some IF/THEN condition I have heard people talking about that could be applied.

Kate

 

[MARK858]

@Katidyd, can you please post some data where the formula Dave Patton posted doesn't give the correct answer after you have added your range of holidays.