Exceljet

Quick, clean, and to the point

Series of dates by custom days

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

To generate a dynamic series of dates that include only certain days of the week (i.e. only Tuesdays and Thursdays) 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,"1010111")

The formula returns only Tuesdays and Thursdays as the formula is copied down. If the start date in B5 is changed, new dates are dynamically generated.

How the formula works

The WORKDAY.INTL function is normally used to find 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 5 days from now.

One of the the arguments provided to WORKDAY.INTL is called "weekend", and controls 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 to "filter out" days that are not Tuesdays or Thursdays.

In the code, 1's represent weekend days (non-working days) and zeros represent work days, as illustrated with the table in D4:K9. We only want to to see Tuesdays or Thursdays, so we use zeros for these days, and 1's for every other day:

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

As the formula is copied down, the WEEKDAY.INTL outputs only Tuesdays or Thursdays. All dates are formatted with the custom number format " ddd d-mmm-yyyy".

Custom days

To customize which days are returned, just adjust the code. For example to output only Mondays, Tuesdays, or Fridays, you can use:

=WORKDAY.INTL(B5,1,"0101011")
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.