Next working day

In the worksheet shown, column B contains 12 dates. The goal is to calculate the next working day after each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. In other words, the formula should automatically skip weekends and any dates defined as non-working days. 

WORKDAY function

The WORKDAY function takes a date and returns the next working day n days in the future or past. You can use WORKDAY to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days. The generic syntax for WORKDAY looks like this:

=WORKDAY(start_date,days,[holidays])

Where days is a number (n) and holidays is an optional range that contains non-working dates. For this problem we want the next working day, so we provide 1 for days. The formula in D5, copied down, looks like this:

=WORKDAY(B5,1,holidays)

Where holidays is the named range F5:F15, which contains days that should be excluded. The WORKDAY function is fully automatic. Given a valid date, it will add days to the date, skipping weekends and holidays. Named ranges behave like absolute references by default so the range will not change as the formula is copied down. Without a named range, you will need to lock the reference like this:

=WORKDAY(B5,1,$F$5:$F$15)

As the formula is copied down, it returns the next business day after the starting date in column B. Saturdays and Sundays are automatically skipped, as well as any dates that appear in the range F5:F15.

Current date or next workday

There may be situations where you want to return the current date when it's a working day or the next working date if not. To do this, you can adjust the formula like so:

=WORKDAY(B5-1,1,holidays)

Here, we first subtract 1 day from the date inside the WORKDAY function, then feed that date to WORKDAY as the start_date. WORKDAY then moves forward one day to the original date and checks the result. If the original date is a working day, WORKDAY returns the date unchanged. Otherwise, WORKDAY will continue to move forward one day at a time, skipping weekends and holidays along the way, until it finds a valid workday. You can see the result in the worksheet above.

Custom weekends

The WORKDAY function defines a weekend as Saturday and Sunday only. If you need more flexibility on which days of the week are considered weekends or working days, use the WORKDAY.INTL function instead. For example, to calculate the next working day for this example with a standard work week of Monday-Thursday, where weekend days are Friday, Saturday, and Sunday, you can use WORKDAY.INTL like this:

=WORKDAY.INTL(B5,1,"0000111",holidays)

WORKDAY.INTL includes an extra argument called weekend that can be provided as a string of 1s and 0s like "0000111". In this scheme, a 1 indicates a weekend and a 0 indicates a workday. For more details, see How to use the WORKDAY.INTL function.