SOLVED

Combine Workday function with calendar day formula

New Contributor

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(E45,2),1,Holidays[Dates])

 

Where A4 is the first day of the month, RIGHT(E45,2) returns the number for the calendar day (e.g. 16) and Holidays[Dates] is a table containing a list of holidays. This works great if the date falls on a weekend or holiday but otherwise it returns the day after the date I'm looking for. For example:

 

  • If $A$4+RIGHT(E45,2)-1 equals 6/25/2022, the formula returns 6/27/2022 which is correct
  • If $A$4+RIGHT(E45,2)-1 equals 6/16/2022, the formula returns 6/21/2022 but it should return 6/16/2022

Thank you in advance.

4 Replies

@Jennifer_Eggleston 

I don't understand your second example.

In the first place, if $A$4+RIGHT(E45,2)-1 equals 6/16/2022, the formula returns 6/20/2022 for me, not 6/21/2022. Is that because of Juneteenth (I don't live in the USA)?

In the second place, why should the formula return 6/16/2022? It always returns a date after =WORKDAY($A$4+RIGHT(E45,2) = 6/17/2022 in this example.

@Hans Vogelaar 

 

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
best response confirmed by Jennifer_Eggleston (New Contributor)
Solution

@Jennifer_Eggleston 

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!