Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.

[Romy]

Hello, my apologies if this problem has been solved in a previous post - I've tried searching this forum, but couldn't find an answer. Perhaps I wasn't searching correctly.

I am trying to calculate deadlines by subtracting (all calendar) days from a start date, but the deadline cannot fall on a weekend or statutory holiday. If the end date falls on a weekend or statutory holiday, the deadline is pushed back to the preceding (non-holiday, non-weekend) work day. For example, if the end date falls on a Saturday or Sunday, the deadline will be pushed to the previous Friday (as long as the Friday is not a stat holiday; if the Friday is a stat holiday, then deadline will be pushed back again to the day before - a Thursday). I have the holidays listed out in a table named "Holidays". I am not opposed to using multiple columns - whatever to make this work!

I've come across two formulas for subtracting dates that only work part of the time.

1. =WORKDAY(start_date + Days*,1,Holidays). *the Days value is always minus 1 of what you want to calculate. Example: calculating 5 days before a start date, cell B1 is "Days" and has a value of -6. =WORKDAY(start_date + B1,1,Holidays)
This one doesn't always work because if the end date falls on a weekend or holiday, the end date gets pushed forward to the next working day, rather than backwards to the preceding work day.

2. =WORKDAY.INTL(start_date,Days,1, Holidays)
This formula doesn't always work because it does not count weekends or holidays in the calculations, producing a date that is too early.

Appreciate any help!

 

[jasonb75]

The easiest way to make this work (that I've found) is to go forward to the next working day, then return to the previous.

=WORKDAY(WORKDAY(startday, days + 1),-1,holidays)

=WORKDAY.INTL(WORKDAY.INTL(startday, days + 1,1),-1,1,holidays)

Note that for your task WORKDAY and WORKDAY.INTL will both give you the same results. The only difference between them is that WORKDAY only allows for a weekend of Saturday and Sunday whilst WORKDAY.INTL allows you to customise the weekend. Ideal for situations like 6 day weeks, work all weekend but not on a Wednesday, etc.

 

[Romy]

Hi Jasonb75, thanks so much for your response and for explaining the purpose of WORKDAY.INTL - I wasn't aware of this. Since I'm working with a regular weekend situation, I'll use the WORKDAY formula. I tested =WORKDAY(WORKDAY(startday, days + 1),-1,holidays) and unfortunately, it only seems to work when the end date falls on a Sunday.

Thank you so much for your response anyway! I do appreciate it.

 

[jasonb75]

Could you give some examples of what you mean using actual dates please?

I've used it before with the correct results so it could be that I'm missing something in what you are trying to do.

 

[Alex Blakenburg]

Doesn't this do what you want ?
Reference: Previous working day

Book1
	A	B	C	D	E	F
1
2
3	Start Date	Days	Deadline	Comparison if you just add the days		Test Holidays
4	Mon 1/04/2024	5	Wed 3/04/2024	Sat 6/04/2024		4/04/2024
5						5/04/2024
6
Sheet1

Cell Formulas
Range	Formula
C4	C4	=WORKDAY(A4+B4,-1,F4:F7)
D4	D4	=A4+B4

 

[Romy]

Could you give some examples of what you mean using actual dates please?

I've used it before with the correct results so it could be that I'm missing something in what you are trying to do.

I should've said that the formula works when the deadline lands on a holiday as well as Sundays. The formula seems to skip weekend days when counting, but I want to include weekend days when counting, I just don't want the deadline to fall on a weekend (or holiday).

I've attached two photos - one of a screenshot of the Excel sheet and one of a June-Aug calendar for manually counting the days out.

Not sure what the resolution of the photos are, so I've typed out a sample from the Excel sheet:
Test holidays: Friday, July 26, 2024 and Monday, July 29, 2024
Start Date: Mon, July 1, 2024; Formula Deadline: Mon, June 24, 2024; Correct Deadline: Wed, June 26, 2024
Start Date: Tue, July 2, 2024; Formula Deadline: Tue, June 25, 2024; Correct Deadline: Thu, June 27, 2024
Start Date: Wed, July 3, 2024; Formula Deadline: Wed, June 26, 2024; Correct Deadline: Fri, June 28, 2024
Start Date: Thu, July 4, 2024; Formula Deadline: Thu, June 27, 2024; Correct Deadline: Fri, June 28, 2024
Start Date: Fri, July 5, 2024; Formula Deadline: Fri, June 28, 2024; Correct Deadline: Fri, June 28, 2024 - correct

The pattern repeats until we get to the test holidays.
Start Date: Tues, July 30; Formula Deadline: Tues, July 23; Correct Deadline: Thu, July 25
Start Date: Wed, July 31; Formula Deadline: Wed, July 24; Correct Deadline: Thu, July 25
Start Date: Thu, August 1; Formula Deadline: Thu, July 25; Correct Deadline: Thu, July 25 - correct
Start Date: Fri, August 2; Formula Deadline: Thu, July 25; Correct Deadline: Thu, July 25 - correct
Start Date: Mon, August 5; Formula Deadline: Thu, July 25; Correct Deadline: Wed, July 31
Start Date: Tue, August 6; Formula Deadline: Tue, July 30; Correct Deadline: Thu, August 1
Start Date: Wed, August 7; Formula Deadline: Wed, July 31; Correct Deadline: Fri, August 2
Start Date: Thu, August 8; Formula Deadline: Thu, August 1; Correct Deadline: Fri, August 2

Thanks again, Jasonb75!

 

[Romy]

Doesn't this do what you want ?
Reference: Previous working day

Book1
	A	B	C	D	E	F
1
2
3	Start Date	Days	Deadline	Comparison if you just add the days		Test Holidays
4	Mon 1/04/2024	5	Wed 3/04/2024	Sat 6/04/2024		4/04/2024
5						5/04/2024
6
Sheet1

Cell Formulas
Range	Formula
C4	C4	=WORKDAY(A4+B4,-1,F4:F7)
D4	D4	=A4+B4

Hi Alex, thanks so much for your response! Unfortunately that formula doesn't always work (see first attached screenshot of Excel sheet).

However, your message made me think of way to do this over a two columns, and then Mr. Excel had a YouTube video for this formula (

).

A2 = Start Date
B2 = Days to subtract
C2 = Deadline with formula =A2+B2
D2 = Amended Deadline with formula =IF(NETWORKDAYS(C2,C2,Holidays)=1,C2,WORKDAY(C2,-1,Holidays))

Thank you very much, I wouldn't have thought to search for this without your post!

 

[Alex Blakenburg]

Appreciate the comprehensive feedback. I would recommend marking your latest post #7 as the solution, I am sure others will find it helpful.

Since you have MS 365 you could do away with the helper column and use the LET command to help document the logic eg

=LET(CalcDeadline, A2+B2,
            IF(NETWORKDAYS(CalcDeadline, CalcDeadline, Holidays)=1,
                 CalcDeadline,
                 WORKDAY(CalcDeadline, -1, Holidays)))

If you want to increase the level of documentation maybe even.

=LET(CalcDeadline, A2+B2,
            WD_Deadline, IF(NETWORKDAYS(CalcDeadline, CalcDeadline, Holidays)=1,
                                                 CalcDeadline,
                                                 WORKDAY(CalcDeadline, -1, Holidays)),
             WD_Deadline)

 

[Romy]

Oh wow, thank you, Alex! I also wasn't aware of the "LET" command. Thank you kindly!