extract year value and rearrange

[freeriding]

Hallo! i have a column with dates (eg 1/1/2019) and want to extract the years, and put them in a row from oldest to newest.

eg. i have in a column these dates:

• 1/1/2017
• 1/3/2017
• 1/1/2018
• 2/1/2018
• 1/1/2019
• 5/1/2019

and want to extract the years in 3 consecutive cells in a row from oldest to newest: 2017 | 2018 | 2019

 

[XOR LX]

Hi,

Assuming those values are in A1:A6, array formula**:

=IFERROR(SMALL(IF(FREQUENCY(YEAR($A1:$A6),YEAR($A1:$A6)),YEAR($A1:$A6)),COLUMNS($A1:A1)),"")

and copied to the right until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[freeriding]

awesome, thank you very much! The only problem, is when i have some blank rows between the dates, then i get a 1900 value in the array formula! Why is that and how do i fix this?

eg. i get in the above example: 1900 | 2017 | 2018 | 2019

 

[XOR LX]

Ok, then use:

=IFERROR(SMALL(IF(FREQUENCY(IF($A1:$A6<>"",YEAR($A1:$A6)),YEAR($A1:$A6)),YEAR($A1:$A6)),COLUMNS($A1:A1)),"")

Regards

 

[freeriding]

awesome! thank you

 

[XOR LX]

You're welcome!

Cheers