Cantrecallmyusername
Board Regular
- Joined
- May 24, 2021
- Messages
- 50
- Office Version
- 365
- Platform
- 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
Weekday formula handles the instance when the date is returning a Sat/Sun but I cannot seem to add the holidays to this formula
Any ideas on this one?
Thanks in advance.
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.