Hits: 14

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

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

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

Western Australian Center for Applied Machine Learning & Data Science – Membership