Sequence of custom days

The goal is to generate a series of "custom" days of the week based on a start date entered in cell B5. For example, you might want to list sequential dates for any of the following combinations of days:

• Mondays, Wednesdays, and Fridays (as shown)
• Tuesdays, Thursdays, and Saturdays
• Tuesdays and Thursdays
• Mondays and Fridays

The number of dates to create (n) is entered in cell B8. If the start date or the number of dates to create is changed, the dates should be recalculated. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY.INTL function. In older versions of Excel, you can also use the WORKDAY.INTL function and a more manual approach, as explained below.

WORKDAY.INTL function

The WORKDAY.INTL function takes a start date and returns the next workday based on a given offset value provided as the days argument. WORKDAY.INTL will automatically exclude Saturday and Sunday and can optionally exclude dates that are holidays. The generic syntax for WORKDAY.INTL looks like this:

=WORKDAY.INTL(start_date,days,[weekend],[holidays])

• start_date - the date to start from
• days - the number of days to move forward or back
• weekend - a code to specify which days are weekends
• holidays - a list of dates that are non-working days

One of WORKDAY.INTL's tricks is that it can be configured to treat any day of the week as a workday by providing the weekend argument as a 7-digit code that covers all seven days of the week, Monday through Saturday. In this scheme, a 1 indicates a weekend (non-working) day, and a 0 indicates a workday. The table below shows sample codes in column B and the workdays they define in column J:

This method is more flexible since it can define any day of the week as a weekend or workday. For example, to get the next workday that is a Monday, Wednesday, or Friday after a date in cell A1, you can use a formula like this:

=WORKDAY.INTL(A1,1,"0101011") // Mon, Wed, Fri

To get the next Tuesday or Thursday, you can use a formula like this:

=WORKDAY.INTL(A1,1,"1010111") // Tue, Thu

You can also use this feature to create a list of weekends only.

Note: weekend must be entered as a text string surrounded by double quotes ("") when using this feature.

Current Excel version

In the current version of Excel (Excel 2019+), the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY.INTL function. In the workbook shown above, the formula in cell D5 is:

=WORKDAY.INTL(B5-1,SEQUENCE(B8),"0101011")

The inputs provided to WORKDAY.INTL are as follows:

• start_date - B5-1 (the day before the start date)
• days - array created by SEQUENCE (see below)
• weekend - "0101011" (code allowing Mon, Wed, and Fri only)
• holidays - omitted (could be supplied as a range of dates)

Working from the inside out, the SEQUENCE function is used to generate a sequential array of n numbers, where n comes from cell B8. With the number 12 in cell B8, SEQUENCE generates an array like this:

=SEQUENCE(B8)
=SEQUENCE(12)
={1;2;3;4;5;6;7;8;9;10;11;12}

Next, the start_date is calculated by subtracting 1 from the date in B5. We do this because we want to force WORKDAY.INTL to check the start date as well. If it is a Monday, Wednesday, or Friday, it should be included in the list:

=B5-1
=45170-1
=45169 // 31-Aug-2023

Excel dates are just large serial numbers, so the result is 45169, a number that represents 31-Aug-2023, the day before the start date in B5.  Simplifying, we have:

=WORKDAY.INTL(45169,{1;2;3;4;5;6;7;8;9;10;11;12},"0101011")

Because days is given as an array with 12 numbers, WORKDAY.INTL returns 12 dates filtered by the code "0101011", which treats only Mondays, Wednesdays, or Fridays as workdays. With this configuration, WORKDAY.INTL, returns the next 12 Saturdays and Sundays after 31-Aug-2023, skipping all Saturdays, Sundays, Tuesdays, and Thursdays. For more details on WORKDAY.INTL, see How to use the WORKDAY.INTL function.

Tuesdays and Thursdays

To create a list of Tuesdays and Thursdays, just update the weekend code like so:

=WORKDAY.INTL(B5-1,SEQUENCE(B8),"1010111") // Tue and Thu

List custom days between dates

To adapt this formula to list all Mondays, Wednesdays, and Fridays between two given dates (start and end), you can use the same basic idea in a formula like this:

=LET(dates,SEQUENCE(end-start+1,1,start),FILTER(dates,WORKDAY.INTL(dates-1,1,"0101011")=dates))

See this page for a detailed explanation of how this approach works.

Legacy Excel

In older versions of Excel, there is no SEQUENCE function, so we don't have a simple way to request 12 dates at once. One simple solution is to set up the worksheet so that we can "drag copy" a formula that will return the next Saturday or Sunday after the "cell above". First, enter this formula in cell D5:

=WORKDAY.INTL(B5-1,1,"0101011")

• start_date - B5-1 (day before the start date)
• days - 1 (i.e. next date)
• weekend - "1111100" (code allowing Mon, Wed, and Fri only)

To get the start_date, we subtract 1 day from the date in B5 to force WORKDAY.INTL to evaluate the start date in B5 like other dates. If the date in cell B5 is a Saturday or Sunday, it will be returned by the formula above. Otherwise, the next Saturday or Sunday will be returned. As explained above, the weekend argument is given as the code "1111100", which tells WORKDAY.INTL to treat only Mondays, Wednesdays, and Fridays as workdays. Next, in cell D6, enter the formula below and copy the formula down as needed:

=WORKDAY.INTL(D5,1,"0101011")

As the formula is copied down, it begins with the date in the "cell above" and returns the next Monday, Wednesday, or Friday. Because the formula in cell D6 refers to the start date in cell B5, cell B5 still drives all results. The downside of this approach is that the formula in D5 is different from the formulas in cell D6 and below, so you must take care to keep them separate.

Conclusion

The WORKDAY.INTL function will calculate the next working day and can be customized with the "weekend" argument to treat any day of the week as a weekend or workday. The SEQUENCE function can be used within WORKDAY.INTL to create a dynamic list of custom days based on a start date and the number of dates you want. You can also combine WORKDAY.INTL with SEQUENCE and the FILTER function to list custom days between two given dates. In older versions of Excel without SEQUENCE, you can use a more manual approach to get the same results.