# Excel formula for Beginners – How to get Sequence of years in Excel

Hits: 6 # (Excel examples for Beginners)

In this end-to-end excel example, you will learn – Excel formula for Beginners – How to get Sequence of years in Excel.

### 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.