which outputs a series of 12 dates, incremented by one month, beginning on May 1, 2019.
Note: this is an array formula and must be entered with control + shift + enter.
How this formula works
The EDATE function moves forward or backwards in time in one-month increments from a given start date. The SEQUENCE function is a dynamic array function that can generate multiple results that "spill" onto the worksheet in a "spill range".
SEQUENCE can generate results in rows, columns, or both. In this case, SEQUENCE is configured to output an array of numbers that is 12 rows by 1 column:
The start is zero, and step value defaults to 1, so SEQUENCE outputs an array like this:
This array is returned to as the months argument inside the EDATE function. EDATE then returns 12 dates, beginning with the date in B5. Excel dates are stored as serial numbers, so the output looks like this:
When these results are formatted as dates, the result is 12 dates incremented by one month, beginning May 1, 2019.
Note: Unlike some other functions (e.g. the DATE function) the EDATE function does not automatically spill into other cells when delivering more than one result. As a workaround, you can enter as a multi-cell array formula. You must select all 12 cells first to enter or edit the formula.
End of month
To generate a series of "end of month" dates, you use the EOMONTH function instead of EDATE. The formula in F5 is:
To generate a series of dates by day, you can use the SEQUENCE function . In the example shown, the formula in E5 is: = SEQUENCE ( 12 , 1 , C4 , 1 ) which generates a series of 12 dates, beginning with May 1, 2019, the date in C4. How this formula...
To generate a series of dates by year, you can use the SEQUENCE function together with YEAR , MONTH , and DAY functions. In the example shown, the formula in E5 is: = DATE ( SEQUENCE ( 12 , 1 , YEAR ( B5 )), MONTH ( B5 ), DAY ( B5 )) which generates...
The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the...
The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.