Sequence of days

The goal is to generate a series of sequential dates with a formula. In the current version of Excel, the easiest method is to use the SEQUENCE function. SEQUENCE can return all dates at the same time into a range on the worksheet. In older versions of Excel without the SEQUENCE function, you can enter a formula, then manually copy the formula down as needed. Both approaches are explained below.

Background study

• How to use the SEQUENCE function - overview
• The SEQUENCE function - 3 min video
• SEQUENCE of dates - 3 min video
• Dynamic Array Formulas - Video training

SEQUENCE function

The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range". SEQUENCE can generate numeric sequences in rows, columns, or rows and columns. The generic syntax for the SEQUENCE function looks like this:

=SEQUENCE(rows,[columns],[start],[step])

Rows is the number of rows to return, columns is the number of columns to return, start is the starting value, and step is the increment to use between values. In this example, the goal is to generate a sequence of 12 dates, starting with the date in cell B5. To generate the dates, the formula in cell D5 is:

=SEQUENCE(B8,1,B5)

The inputs provided to SEQUENCE are as follows:

• rows - B8 (12)
• columns - 1
• start - B5 (1-Sep-2023)
• step - omitted, defaults to 1

Because dates in Excel are just serial numbers, and the date in B5 is represented by the number 45170 in Excel's date system, SEQUENCE returns an array that contains 12 large sequential numbers like this:

{45170;45171;45172;45173;45174;45175;45176;45177;45178;45179;45180;45181}

Each number in this array represents a date, starting with September 1, 2023. The array lands in cell D5 and spills into the range D5:D16. When these numbers are formatted as dates, they display 12 consecutive dates beginning on September 1, 2023, and ending on September 12, 2023. If the start date in cell B5 is changed, SEQUENCE will automatically return a new set of dates. 

For more details, see How to use the SEQUENCE function.

All dates between two dates

The formula above generates a set number of dates, provided as the rows argument. To adapt the formula to generate all the dates between two a start date and an end date you can adapt the formula like this:

SEQUENCE(end-start+1,1,start)

Where the inputs to SEQUENCE are:

• rows - start date - end date + 1
• columns - 1
• start - start date

Older versions of Excel

In older versions of Excel, there is no SEQUENCE function. However, if you want to generate a sequence of dates with a formula, you can use a generic formula like this:

=A1+1

Where A1 contains a start date. This works because dates in Excel are large serial numbers so it is possible to create and manipulate dates with normal math operations. In this formula above, adding 1 to a date entered in cell A1 is the same as adding 1 day. To adapt the SEQUENCE example above to an older version of Excel, enter this formula in cell D5:

=B5

This formula simply gets the start date in cell B5. Then in D6, enter this formula:

=D5+1

And copy the formula down as needed. Each subsequent formula creates a new date incremented by one day. One key difference in this approach is that it is not possible to dynamically change the number of dates created as we can with the SEQUENCE function. Instead, the dates must be copied manually.

Workdays only

The formula above will output will list all days in the sequence, including weekends and holidays. To generate a sequence of workdays only, you can use the SEQUENCE function with the WORKDAY.INTL function, as explained on this page.