Date is workday

In this example, the goal is to test a date to determine whether it is a workday. In Excel, you can use either the WORKDAY function or its more flexible sibling WORKDAY.INTL to accomplish this task.

WORKDAY function

The WORKDAY function calculates a date in the future or past that is, by definition, a workday. WORKDAY automatically excludes weekends (Saturday and Sunday) and can optionally exclude holidays. WORKDAY accepts 3 arguments: start_date, days, and (optionally) holidays. The generic syntax looks like this:

=WORKDAY(start_date,days,[holidays])

For example, if we provide WORKDAY with the date May 31, 2024 (a Friday), and ask for a workday 1 day in the future, WORKDAY skips Saturday and Sunday and returns June 3, 2024:

=WORKDAY("31-May-2024",1) // returns "3-June-2024"

In this example, we are not providing holidays. If we have a list of dates that are holidays we can provide them like this:

=WORKDAY("31-May-2024",1,holidays) // returns "3-June-2024"

Where "holidays" is a previously defined named range, or a simple range like G5:G7, usually entered as an absolute reference like $G$5:$G$7 to prevent changes when the formula is copied.

Testing for a workday

Since in this problem we want to check a single date and get a TRUE or FALSE result, we would ideally use WORKDAY with days set to zero in a simple formula like this:

=WORKDAY(date,0,holidays)=B5

However, this doesn't work, since WORKDAY will not evaluate a date when days is zero. It always returns the original start date, even if it is a weekend or holiday. The solution is to supply the start_date as a simple calculation that subtracts 1 like this:

=WORKDAY(B5-1,1,holidays)=B5

This causes WORKDAY to step back one day, then add 1 day to the result, taking into account weekends and holidays. Effectively, we are "tricking" WORKDAY into evaluating the original date as the start_date. When the date falls on a weekend or holiday, WEEKDAY will automatically adjust the date forward to the next working day. If the date is a workday, it will remain unchanged. Then we compare the original start date in cell B5 to the result of the WORKDAY function. If the dates are the same (i.e. the result from WORKDAY is the same as the date in B5) we know we have a workday and the formula returns TRUE. If not, WORKDAY has shifted the date (which means it is a non-working day) and the formula returns FALSE. This is the formula used in cell D5 of the worksheet shown above.

Shading non-workdays with conditional formatting

In the worksheet shown, we are also shading non-workdays in gray with conditional formatting, triggered by this formula:

=WORKDAY(B5-1,1,holidays)<>B5

This formula is almost the same, but notice that we are comparing the result from WORKDAY with the original date using the "not equal to" operator ("<>") instead of the "equal to" ("=") operator. This reverses the operation of the formula so that it returns TRUE when a date is not a workday and FALSE when a date is a workday. We do this because we want to shade non-workdays.

Ensure a calculated date falls on a workday

If you are returning a date with another formula and want to make sure the date is a workday, you can use a formula like this:

=WORKDAY(calculated_date-1,1,holidays)

The idea is the same as above - we subtract one day from the date, then ask WORKDAY to give us the next working day.

Note: if you need to evaluate workdays with a custom workweek schedule, where weekends are not Saturday and Sunday, use the more flexible WORKDAY.INTL function.