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.

Explanation

The goal is to generate a series of dates by month based on a given start date. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the EDATE function like this:

=EDATE(B5,SEQUENCE(12,1,0))

The result is a series of 12 dates, incremented by one month, beginning on June 1, 2023, and ending on May 1, 2024. At a high level, this formula uses the EDATE function to return 12 dates one month apart, and it uses the SEQUENCE function to create the numeric array needed to perform this operation in one step.

EDATE function

The EDATE function moves forward or backward in time in one-month increments from a given start date. The generic syntax for EDATE looks like this:

=EDATE(start_date,months)

Inside EDATE, start_date is any valid Excel date, and months is the number of months to add or subtract from start_date . For example, with the date June 1, 2023, in cell B5, EDATE works like this:

=EDATE(B5,-2) // returns April 1, 2023
=EDATE(B5,-1) // returns May 1, 2023
=EDATE(B5,0) // returns June 1, 2023
=EDATE(B5,1) // returns July 1, 2023
=EDATE(B5,2) // returns August 1, 2023
=EDATE(B5,3) // returns September 1, 2023

The challenge in this problem is that we want to return 12 dates at the same time. One way to accomplish this is to use the SEQUENCE function to generate a numeric array we can plug into EDATE.

For more details on EDATE, see: How to use the EDATE function .

SEQUENCE function

The SEQUENCE function is designed to generate numeric sequences in rows and/or columns. The generic syntax for SEQUENCE looks like this:

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

In the example shown, we use SEQUENCE to generate 12 sequential numbers like this:

SEQUENCE(12,1,0)

Here, rows is 12, columns is 1, and start is 0. The reason we want this array to begin with zero is that we want to include the start date in the final result. With this configuration, SEQUENCE will return an array like this:

{0;1;2;3;4;5;6;7;8;9;10;11}

This array is then provided to the EDATE function as the months argument, as described below.

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

EDATE + SEQUENCE

The array created by SEQUENCE is returned as the months argument inside the EDATE function:

=EDATE(B5,{0;1;2;3;4;5;6;7;8;9;10;11})

EDATE then returns 12 dates, beginning with the date in B5. Because Excel dates are stored as large serial numbers , the result is an array like this:

{45078;45108;45139;45170;45200;45231;45261;45292;45323;45352;45383;45413}

This array lands in cell D5 and spills into the range D5:D16. When this range is formatted with date formatting, Excel will display 12 dates beginning on June 1, 2023, and ending on May 1, 2024.

End of month

To generate a series of “end of month” dates, you can use the EOMONTH function instead of EDATE like this:

=EOMONTH(B5,SEQUENCE(12,1,0))

EOMONTH works just like EDATE, except it always returns a date at the end of the month, regardless of the date provided.

Month names only

To generate a list of month names (instead of actual dates), you can wrap the formula above in the TEXT function :

=TEXT(EDATE(B5,SEQUENCE(12,1,0)),"mmmm")

The TEXT function will use the custom number format “mmmm” to convert each date into a text string equal to the month name. The format “mmmm” tells the TEXT function to extract the full month name from a given date.

Older versions of Excel

In older versions of Excel, there is no SEQUENCE function. This means we don’t have an easy way to calculate and return 12 dates all at once. However, you can still generate a series of dates by month with a more manual approach like this:

Dates by month in legacy Excel - 1

Here, the start date is hardcoded into cell B5. The formula in cell B6, copied down, is:

=EDATE(B5,1)

This formula uses the EDATE function to add one month to the date in cell B5. As the formula is copied down, it returns a date one month after the date in the previous row.