Exceljet

Quick, clean, and to the point

Sequence of years

Excel formula: Sequence of years
Generic formula 
=DATE(SEQUENCE(12,1,YEAR(A1)),MONTH(B5),DAY(A1))
Explanation 

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 a series of 12 dates, incremented by one year, beginning with May 1, 2019.

How this formula works

The SEQUENCE function is a dynamic array function that can generate multiple results. Like other dynamic array functions, SEQUENCE outputs an array of 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:

SEQUENCE(12,1,YEAR(B5))

The starting number is the year value from B5, and the step value defaults to 1, so SEQUENCE outputs an array like this:

{2019;2020;2021;2022;2023;2024;2025;2026;2027;2028;2029;2030}

This array is returned to as the year argument inside the DATE function, which causes results to spill into the range D5:D16. MONTH and DAY values are picked up directly from the date in B5:

MONTH(B5),DAY(B5)

When formatted as dates, the final result is 12 dates, one year apart, beginning with May 1, 2019.

Year only option

To use SEQUENCE to output years only, based on the same starting date, the formula in F5 is:

=SEQUENCE(12,1,YEAR(B5))

As before, SEQUENCE is configured to output 12 numbers, beginning with the year in B5, incremented by 1. The final results spill into F5:F16.

This example uses "Dynamic Arrays", a beta feature in Excel currently available through the Office Insiders program. Expected release in 2019.
Author 
Dave Bruns

Excel Formula Training

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.