Previous working day

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

WORKDAY function

The WORKDAY function takes a date and returns the "next" working day n days in the future or the past. You can use the WORKDAY function to calculate things like project start dates, delivery dates, and finish 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 previous 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 dates that should be excluded. The WORKDAY function is fully automatic. Given a valid date, it will move forward or backward by n days, skipping weekends and holidays, until it lands on a working day. Notice we provide a negative 1 for days to move backward. Also, note that we have provided the holidays argument as the named range "holidays" (F5:F15). Without a named range, use an absolute references like $F$5:$F$15:

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

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

Current or previous workday

A common scenario in business is that you have a list of dates that may or may not be working days, and you want to shift dates that are not working days back to the previous working day and leave the other dates alone. In that case, you can adjust the formula as follows:

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

In this version of the formula, we add 1 day to the date inside the WORKDAY function. WORKDAY then moves back one day to the original date and checks the result. If the original date is a working day, WORKDAY returns it. Otherwise, WORKDAY will continue moving back one day at a time until it finds a valid workday, skipping weekends and holidays along the way. You can see the result of this alternate formula in the screen above. For a practical example of this approach, see this formula for semimonthly pay dates.

Custom weekends

The WORKDAY function defines a weekend as Saturday and Sunday only. If you need to provide a more custom workday schedule, switch to the WORKDAY.INTL function instead. For example, to calculate the previous working day in a 4-day workweek where weekends are Friday, Saturday, and Sunday, you can use WORKDAY.INTL like this:

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

WORKDAY.INTL includes an optional argument called weekend that can be provided as a string of 1s and 0s like "0000111". In this scheme, the week begins on Monday and there are 7 characters one for each day of the week. A 1 indicates a weekend and 0 indicates a workday. For more details, see How to use the WORKDAY.INTL function.