where “holidays” is the named range E5:E6. The formula above returns TRUE, since Monday, Dec. 21, 2015 is a workday. 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: Note - if you need custom weekends, use the WORKDAY.INTL function.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
