Exceljet

Quick, clean, and to the point

Series of dates by weekends

Excel formula: Series of dates by weekends
Generic formula 
=IF(WEEKDAY(date)=7,date+1,date+(7-WEEKDAY(date)))
Explanation 

If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions.

How the formula works

In the example, B6 is the hard-coded start date and the formula in B7 is:

=IF(WEEKDAY(B6)=7,B6+1,B6+(7-WEEKDAY(B6)))

To solve this formula, Excel first calculates the weekday value for the date in B6. By default, weekday will return 1 for Sunday and 7 for Saturday. Next, Excel tests the weekday inside the IF statement, using B6=7 as the logical test. If B6 = 7, the date in B6 is a Saturday and the result if true is returned: B6 + 1. So, if B6 is a Saturday, the formula returns the next day (a Sunday).

If not, the result if false is returned:

B6+(7-WEEKDAY(B6))

To solve this part of the formula, Excel calculates the weekday value of B6, then subtracts that value from 7. The result is added to B6. So, for Monday through Friday, this looks like this:

B6+(7-2) = B6+5 <-- Mon
B6+(7-3) = B6+4 <-- Tue
B6+(7-4) = B6+3 <-- Wed
B6+(7-5) = B6+2 <-- Thu
B6+(7-6) = B6+1 <-- Fri

Note: you'll need to supply a date at least one day before the first Saturday you want to generate.

An easier way with WORKDAY.INTL

Jill in the comments below suggested this excellent, easy alternative to the formula above:

=WORKDAY.INTL(E6,1,"1111100")

This formula uses the "mask" option available in WORKDAY.INTL function to only allow Sat and Sun days.

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.