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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
How about
Excel Formula:
=WORKDAY(B2-1,1,Holidays!B2:B12)
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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