Explanation

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 .

Explanation

In this example, the goal is to generate a list of leap years between a given start year and end year. The worksheet is set up so that the start year is an input in cell B5 and the end year is an input in cell B8. If either value changes, the formula should generate a new list of leap years. In the current version of Excel, the easiest way to do this is with the SEQUENCE function and the FILTER function in a formula like this:

=LET(
start,B5,
end,B8,
years,SEQUENCE(end-start+1,,start),
leaps,(MOD(years,400)=0)+((MOD(years,4)=0)*(MOD(years,100)<>0)),
FILTER(years,leaps))

At a high level, this formula uses the SEQUENCE function to generate a list of all years between the start and end. Then it checks for leap years using logic implemented with the MOD function and filters out all non-leap years. At the start, the LET function defines four variables:

  • start - the start year entered in cell B5
  • end - the end year entered B8
  • years - a sequence of all years between the start and end year
  • leaps - a logical test that flags all leap years

Using LET this way keeps the formula efficient and readable. For example, the calculation for years (see below) only runs one time, even though it is used four times in the formula.

Generating a list of years

The next step in solving this problem is to generate a complete list of all years between the start and end years. We can do this with the SEQUENCE function , which is designed to create numeric arrays. For example, to generate the numbers 1 through 10, you can use SEQUENCE like this:

=SEQUENCE(10) // returns {1;2;3;4;5;6;7;8;9;10}

To create a list of numbers between a given start and end , we can use a generic pattern like this:

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

With the start year given as 1980 and the end year given as 2030, we have:

=SEQUENCE(end-start+1,,start)
=SEQUENCE(2030-1980+1,,1980)
=SEQUENCE(50+1,,1980)
=SEQUENCE(51,,1980)

The result is an array with the 51 years between 1980 and 2020, inclusive:

{1980;1981;1982;1983;1984;1985;1986;1987;1988;1989;1990;1991;1992;1993;1994;1995;1996;1997;1998;1999;2000;2001;2002;2003;2004;2005;2006;2007;2008;2009;2010;2011;2012;2013;2014;2015;2016;2017;2018;2019;2020;2021;2022;2023;2024;2025;2026;2027;2028;2029;2030}

This array is then assigned to the years variable defined by LET.

Testing for leap years

The next step is to test each year in years to identify leap years. This is done with the following snippet:

(MOD(years,400)=0)+((MOD(years,4)=0)*(MOD(years,100)<>0))

This logic here is based on the following leap year rule, which is explained in detail on this page :

To be a leap year, the year number must be divisible by four, except for end-of-century years, which must be divisible by 400. This means that 2000 is a leap year, but 1700, 1800, and 1900 are not leap years.

The test is implemented with Boolean algebra and the MOD function as follows:

  1. If the year is divisible by 400, it’s a leap year (TRUE).
  2. Or if the year is divisible by 4 and not divisible by 100, it’s a leap year (TRUE)
  3. Otherwise, the year is not a leap year (FALSE).

The result is an array of 51 TRUE and FALSE values. The TRUE values in this array correspond to leap years in the original years array, and the FALSE values indicate non-leap years. The array is assigned to the leaps variable defined by LET.

Removing the non-leap years

The final step is removing non-leap years, which is done with the FILTER function like this:

FILTER(years,leaps)

Recall that the two arrays, years and leaps , are the same size; each array contains 51 rows. The FILTER function uses the leaps array to filter out non-leap years. The final result is an array that contains only leap years. This array lands in cell D5 and spills down the worksheet. If the values for start (B5) or end (B8) are changed, the entire process is repeated and a new list of leap years is generated.

Pro-tip: implement AND and OR

=OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0))

Can we use this same approach here? We can, but we need to adjust the formula first. AND and OR are “aggregating functions”, which means they return a single aggregated result. This won’t work in this case, because we are running a test on 51 years and need to get 51 results back in a single array so FILTER can use the array to remove non-leap years. The solution is to implement the AND and OR inside the BYROW function like this:

=LET(
start,B5,
end,B8,
years,SEQUENCE(end-start+1,,start),
leaps,BYROW(years,LAMBDA(y,OR(MOD(y,400)=0,AND(MOD(y,4)=0,MOD(y,100)<>0)))),
FILTER(years,leaps))

In this version, leaps is defined with BYROW like this:

BYROW(years,LAMBDA(y,OR(MOD(y,400)=0,AND(MOD(y,4)=0,MOD(y,100)<>0))))

BYROW processes values in a row-by-row fashion, so each year (defined as y above) is tested separately. The result for each year is a single TRUE or FALSE value. The BYROW function packages the results into a single array, which we hand off to FILTER as before.

There is no difference in the output from both versions of the formula. The choice of which to use is a personal preference.