Date is workday

The WORKDAY function calculates dates in the future or past that are (by definition) "workdays". WORKDAY automatically excludes weekends and can optionally exclude holidays. WORKDAY accepts 3 arguments: start_date, days, and (optionally) holidays. The generic syntax looks like thisL

=WORKDAY(start_date,days,[holidays])

Since we want to check a single date and get a TRUE or FALSE result, we would ideally use WORKDAY with the simple formula below:

=WORKDAY(date,0)=B5

However, this doesn't work, since WORKDAY does not seem to evaluate a date when no offset is present. The solution is supply (date-1) for start_date, 1 for days, and the named range "holidays" (E5:E6) for holidays:

=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 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 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.

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 Satuday and Sunday, use the more flexible WORKDAY.INTL function.