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.

R

Romy

New Member
Joined
Apr 3, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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.
 
Upvote 0
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. 😊
 
Upvote 0
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.
 
Upvote 0
jasonb75 said:
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.
Click to expand...
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!
 

Attachments

  • Excel formula test.png
    Excel formula test.png
    61.3 KB · Views: 24
  • June-Aug 2024 calendar.png
    June-Aug 2024 calendar.png
    21.1 KB · Views: 24
Upvote 0
Alex Blakenburg said:
Doesn't this do what you want ?
Reference: Previous working day

Book1
ABCDEF
1
2
3Start DateDaysDeadlineComparison if you just add the daysTest Holidays
4Mon 1/04/20245Wed 3/04/2024Sat 6/04/20244/04/2024
55/04/2024
6
Sheet1
Cell Formulas
RangeFormula
C4C4=WORKDAY(A4+B4,-1,F4:F7)
D4D4=A4+B4
Click to expand...
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!
 

Attachments

  • Excel formula 2 test.png
    Excel formula 2 test.png
    58.4 KB · Views: 39
  • Excel formula over two columns.png
    Excel formula over two columns.png
    42.2 KB · Views: 38
Upvote 0
Solution
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

Excel Formula: 
=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.
Excel Formula: 
=LET(CalcDeadline, A2+B2,
            WD_Deadline, IF(NETWORKDAYS(CalcDeadline, CalcDeadline, Holidays)=1,
                                                 CalcDeadline,
                                                 WORKDAY(CalcDeadline, -1, Holidays)),
             WD_Deadline)
 
Upvote 0
Oh wow, thank you, Alex! I also wasn't aware of the "LET" command. Thank you kindly! 😊
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
add number of working days to a specific date (Start Date) to have the (End Date)
Replies
5
Views
294
Peter_SSs
Peter_SSs
R
  • Question Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
779
etaf
etaf
L
  • Solved
Expected Delivery Date - Specific Delivery Week Days
Replies
6
Views
388
bobsan42
bobsan42
C
  • Question Question
Finding weekends and holidays within a date range
Replies
1
Views
703
ColinPearsonEIT
C
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
2K
felixstraube
F

Forum statistics

Threads
1,223,914
Messages
6,175,353
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top