Exceljet

Quick, clean, and to the point

Series of dates by workdays

Excel formula: Series of dates by workdays
Generic formula 
=WORKDAY(A1,1,holidays)
Explanation 

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.

How this formula works

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.

=IF(WEEKDAY(date)=6,date+3,date+1)
Author 
Dave Bruns

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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables