Exclude weekend and holidays in date calculation

Cantrecallmyusername

Board Regular
Joined
May 24, 2021
Messages
50
Office Version
  1. 365
Platform
  1. Windows
I have the need to create a formula where I want the date to move to the next Monday if the date is a weekend day (Sat and Sun) and also if the date is included on a list of holidays.
I would like it to move to the next day which is not a Sat or Sun - Monday.

My date is in cell B2
My holidays are on tab Holidays, range B2:B12

I have tried two formulas and they both solve one half of the issue, but I cannot seem to combine them to get the desired result
Workday formula handles the list of dates I have listed as holidays though I cannot move the date forward if it is Sat/Sun

Excel Formula:
=WORKDAY(B2,{0},Holidays!B2:B12)

Weekday formula handles the instance when the date is returning a Sat/Sun but I cannot seem to add the holidays to this formula
Excel Formula:
=IF(WEEKDAY(B2)=6,B2+1,IF(WEEKDAY(B2)=7,B2+2,B2))

Any ideas on this one?
Thanks in advance.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
How about
Excel Formula:
=WORKDAY(B2-1,1,Holidays!B2:B12)
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

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