Exceljet

Quick, clean, and to the point

Series of dates by weekends

Excel formula: Series of dates by weekends
Generic formula 
=WORKDAY.INTL(A1,"1111100")
Explanation 

To generate a dynamic series of dates that are weekends only (Saturday and Sunday), you can use the WORKDAY.INTL function. In the example shown, the date in B5 is a hardcoded start date. The formula in B6 is:

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

This returns only Saturdays or Sundays as the formula is copied down. The list is dynamic – when start date is changed, the new dates are generated.

How the formula works

The WORKDAY.INTL function is normally used to generate dates that are workdays. For example, you can use WORKDAY.INTL to find the next workday that is not a weekend or holiday, or the first workday 10 days from now.

One of the the arguments provided to WORKDAY.INTL is called "weekend", and  indicates which days are considered non-working days. The weekend argument can be provided as a number linked to a preconfigured list, or as a 7-character code that covers all seven days of the week, Monday through Saturday. This example uses the code option.

In the code, 1's represent weekend days (non-working days) and zeros represent work days, as illustrated with the table in D4:K5. We only want to to see Saturdays and Sundays in the output, so use 1 for all days Monday-Friday, and zero for Saturday and Sunday:

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

The days argument is input as 1. As the formula is copied down, the WEEKDAY.INTL function returns the next date that is either Saturday or Sunday.

Another way

If you have an older version of Excel without the WEEKDAY.INTL function, you can an alternative formula based on the WEEKDAY function:

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

By default, WEEKDAY will return 1 for Sunday and 7 for Saturday. If the result is 7, the result is B5+1 (Sunday). If not, the result is

B5+(7-WEEKDAY(B5))

Which resolves like this, depending on day of week:

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

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

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.