The formula above 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.
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:
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.
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.
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, you can use the following formula:
To get the next working day, or next business day, you can use the WORKDAY function. In the example shown, the formula in C4 is: = WORKDAY ( B4 , 1 , holidays ) How this formula works The WORKDAY formula is fully automatic. Given a date and days, it...
To add or subtract workdays days to a date so that you can calculate a date in the future or past that skips weekends (customized) and holidays , you can use the WORKDAY.INTL function. Holidays and weekend are both optional. In the example, the...
To get a date 6 months in the future, on the next work day, you can use a formula based on the WORKDAY function, with help from EDATE. In the example shown, the formula in C6 is = WORKDAY ( EDATE ( B6 , 6 ) - 1 , 1 , B9:B11 ) How this formula works...
The Excel WORKDAY function takes a date and returns the nearest working day in the future or past, based on an offset value you provide. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates...
The Excel WORKDAY.INTL function takes a date and returns the nearest working in the future or past, based on an offset value you provide. Unlike the WORKDAY function, WORKDAY.INTL allows...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.