extract year value and rearrange

freeriding

New Member
Joined
Sep 21, 2015
Messages
18
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
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Sep 21, 2015
Messages
18
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Ok, then use:

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

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,862
Latest member
lbisacca
Top