How to extract each year in a multiyear date range

[SeliM]

Good morning
I have a suite of records with dates over a number of years.
I have the task that includes need to know the years covered by the date range
I have tried to use sumproduct without success
The need is for a formula not vba

Your advice would be most apreciated.
Geoff

 

[Asbestos_Jen]

Try this:

 

[SeliM]

Many thanks
Unfortunately, my data is by columns, so I don't get a lovely unique list like yours.
I get each date converted to year

 

[alansidman]

Geoff
Provide a sample that is representative of your data. Then mark up a mocked up solution that matches your sample data. Please use XL2BB

 

[SeliM]

I'm sorry I've never had success with XL2BB
Sample date by row

Date of Audit:

17/08/2022

17/10/2022

17/02/2024

17/08/2024

16/03/2025

16/08/2027

List of Years
2022
2024
2025
2027

List that adds years if not already in the list

 

[Rick Rothstein]

Since your dates appear to be in ascending order, there is no need for the SORT function. Give this a try (adjust the range to match your actual data's location)...

=TOCOL(UNIQUE(YEAR(B2:G2),1))

 

[SeliM]

Wonderful!

It looks so easy when you people provide a response

A small thing for some a big step for me
I really appreciate both you knowledge and patience

G