Date is workday

An excel formula to date is workday

Related Functions

Sponsored Link

 Date is workday
=WORKDAY(date-1,1,holidays)=date

To determine if a date is a workday or not, you can use a formula based on the WORKDAY function.

In the example shown, the formula in C5 is:

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

which returns TRUE, since Monday, Dec. 21, 2015 is a workday.

How this formula works

The WORKDAY function calculates dates in the future or past that are (by definition) "workdays". In other words, WORKDAY automatically excludes weekends and (optionally) holidays.

WORKDAY accepts 3 arguments: start_date, days, and an (optionally) holidays.

In this case, we supply (date - 1) for start_date, 1 for days, and the named range "holidays" (E5:E6) for holidays.

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. (Unfortunately, the cleaner syntax WORKDAY(date,0) doesn't work).

When the date falls on a weekend or holiday, WEEKDAY will automatically adjust the date forward to the next working day.

Finally, we compare the original start_date to the the result of the WORKDAY function. If the dates are the same (i.e. the result of WORKDAY equals the start_date, the formula returns TRUE. If not, the formula returns FALSE.

Ensure a calculated date falls on a workday

To make sure any calculated date lands on a business day, just use the following formula:

 
=WORKDAY(calc_date-1,1,holidays)

Note - if you need custom weekends, use the WORKDAY.INTL function.

Sponsored Link

0 votes. 0 / 5