You will sometimes need to generate a sequence of dates in Excel. Most often, you will need month-end dates, but no matter what kind of dates you need, the simplest way to generate them will include the EOMONTH function combined with the SEQUENCE function.
The EOMONTH function returns the last day of the month in relation to the start date.
The EOMONTH function syntax is as follows:
= EOMONTH ( start_date; number of months )
The start date is any given date, such as 01.01.2020.
The number of months is an integer number of whole months before or after start_date.
If that number is zero, the end of the start_date month date will be returned.
If that number is 1, the end of the month following the start_date month will be returned.
If that number is -1, the end of the month preceding the start_date month will be returned.
The SEQUENCE function generates a list of sequential numbers.
The SEQUENCE function syntax is as follows:
=SEQUENCE ( rows ; [columns] ; [start] ; [step] )
Rows specifies the number of rows to fill.
Columns specifies the number of columns to fill. If omitted, 1 column is returned.
Start specifies the starting number in the sequence. If omitted, it defaults to 1.
Step defines the increment for subsequent values. If omitted, it defaults to 1.
Rows and columns need to be positive whole numbers, while start and step can be any type of number.
We can generate a series of end-of-month dates either by using the EOMONTH function and a helper column or by nesting the SEQUENCE function as the number of months part of the argument for the EOMONTH function:
We can generate a sequence of the start-of-month dates by adding one more day to the month-end date of the previous month:
We can generate a sequence of dates containing every 10th day of the month by adding 10 more days to the month-end date of the previous month:
We can generate a sequence of dates containing every 5th day from the end of the month by removing 4 days from the month-end date:
We can generate a series of end-of-quarter dates by increasing our sequence step by 3 months:
We can generate a series of end-of-year dates by increasing our sequence step by 12 months:
Dig deeper:
EOMONTH function: calculate past or future date from the starting date
3 thoughts on “Combine EOMONTH with SEQUENCE: generate a sequence of dates”