## odd excel prob

The war between wetware and hardware.
ed
Posts: 36476
Joined: Tue Jun 08, 2004 11:52 pm
Title: The Hero of Sukhbataar

### odd excel prob

I am trying to get the year from a column of dates. I am using right(cell,4) and I get odd results
6/3/2017 2017
8/12/2017 2017
9/5/2017 2017
10/7/2017 2017
12/3/2017 2017
6/xx/2017 2017
2/14/2015 2049
3/8/2015 2071
6/20/2015 2175
9/23/2015 2270
9/20/1988 2406
2/28/2016 2428
4/19/2016 2479
5/29/2016 2519
7/18/2016 2569
7/23/2016 2574
8/7/2016 2589
Cell formats are the same.

Is this another example of the paranormal?
Wenn ich Kultur höre, entsichere ich meinen Browning!
I am caring less day by day

Abdul Alhazred
Posts: 78666
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago

### Re: odd excel prob

Convert the dates to "string" before using the "right" function.
The arc of the moral universe bends towards chaos.
People who believe God or History are on their side provide the chaos.

gnome
Posts: 23126
Joined: Tue Jun 29, 2004 12:40 am
Location: New Port Richey, FL

### Re: odd excel prob

Easier:

Supposing the dates are in column A and starting with row 2, and your Year formula is in B2:

=YEAR(A2)

Copy down. Done.
"If fighting is sure to result in victory, then you must fight! Sun Tzu said that, and I'd say he knows a little bit more about fighting than you do, pal, because he invented it, and then he perfected it so that no living man could best him in the ring of honor. Then, he used his fight money to buy two of every animal on earth, and then he herded them onto a boat, and then he beat the crap out of every single one. And from that day forward any time a bunch of animals are together in one place it's called a zoo! (Beat) Unless it's a farm!"
--Soldier, TF2

Mentat
Posts: 10271
Joined: Tue Nov 13, 2007 11:00 pm
Location: Hangar 18

### Re: odd excel prob

In excel, what you see is not always the same data format that functions see. If in doubt, cast to another format or use a different function.
It's "pea-can", man.

Lapis Sells . . . But Who's Buying?