Forum Discussion
Solved
Combine Workday function with calendar day formula
I have a formula that returns a specific calendar day but, if that day falls on a weekend or holiday, I want it to return the following working day. I've tried this formula: =WORKDAY($A$4+RIGHT(E...
If A4 equals 6/1/2022 and E45 ends in 16:
$A$4+RIGHT(E45,2)-1 = 6/1/2022 + (16-1) days = 6/1/2022 + 15 days = 6/16/2022.
6/16/2022 is a Thursday. The next workday is 6/17/2022.
Perhaps you want
=WORKDAY($A$4+RIGHT(E45,2)-2,1,Holidays[Dates])
This will return 6/16/2022.
Yes, Juneteenth is a holiday for our company and, since it falls on Sunday this year, the holiday is 6/20.
If I use the formula
=WORKDAY($A$4+RIGHT(E45,2)-1,1,Holidays[Dates])
- If A4 is 6/1/2022 and RIGHT(E45,2) is 16 (minus 1 = 15), I get 6/17/2022 but it should be 6/16
- If A4 is 6/1/2022 and RIGHT(E45,2) is 25 (minus 1 = 24), I get 6/27/2022 which is correct
If A4 equals 6/1/2022 and E45 ends in 16:
$A$4+RIGHT(E45,2)-1 = 6/1/2022 + (16-1) days = 6/1/2022 + 15 days = 6/16/2022.
6/16/2022 is a Thursday. The next workday is 6/17/2022.
Perhaps you want
=WORKDAY($A$4+RIGHT(E45,2)-2,1,Holidays[Dates])
This will return 6/16/2022.
- It works! Thank you!