# extract year value and rearrange

#### freeriding

##### New Member
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:

### 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
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
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
Ok, then use:

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

Regards

#### freeriding

##### New Member
awesome! thank you

You're welcome!

Cheers

Replies
5
Views
51
Replies
4
Views
57
Replies
13
Views
161
Replies
13
Views
173
Replies
3
Views
61

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