Series of dates by workdays

To generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can use the WORKDAY function. In the example shown, the formula in B6 is:

=WORKDAY(B5,1,holidays)

where holidays is the named range E5:E6.

Explanation

With a given start date, the WORKDAY function will return a date that is not a weekend or holiday, n days in the future or past. The holidays argument is optional and can be omitted. In the example, B5 a the hard-coded start date. The formula below in B6, copied down, is:

=WORKDAY(B5,1,holidays)

Because the days argument is set to 1, the formula returns the "next working day" on each new row. Holidays are provided as the named range E5:E6, which contains dates. Notice December 24 and 25 are excluded from the list since these days are holidays.

All dates are formatted with the custom number format " ddd d-mmm-yyyy".

Custom weekends

If you need take into account custom weekends (i.e. weekends are Saturday only, Sunday and Monday, etc.) you'll need to switch to the more robust WORKDAY.INTL function, which allows you to set what days of the week are considered are considered weekends, by supplying a weekend argument in the form of a special code, as explained in this example.

Another way

You can also generate a series of workdays with a formula that uses the WEEKDAY function. In this case, the formula tests the start date to see if is a Saturday or not. If so, 3 days are added to the start date. If not, just one day is added.