List unique years from a set of dates in google sheets

1.2k Views Asked by dan At 04 April 2025 at 16:42

I am currently achieving the process of extracting unique years from a range of dates with the use of helper columns shown here:

Is it possible to achieve the same using just a single formula?

Here are the three formulas I am currently using:

S column: =UNIQUE('Earnings Input'!A2:A) T column: =YEAR(S1) U column: =UNIQUE(T1:T)

Appreciate any help I can get!

There are 1 best solutions below

Erik Tyler On 14 May 2022 at 19:46 BEST ANSWER

As there is no link to your spreadsheet, this is untested, but you can try this:

=ArrayFormula(SORT(UNIQUE(YEAR(FILTER('Earnings Input'!A2:A,ISNUMBER('Earnings Input'!A2:A))))))

FILTER( ,ISNUMBER( )) filters in only values that could reasonably be a date.

YEAR gets the years for those entries.

UNIQUE reduces to the unique listing

SORT assures the results will be in ascending order.