odd excel prob

The war between wetware and hardware.
User avatar
Posts: 38424
Joined: Tue Jun 08, 2004 11:52 pm
Title: G_D

odd excel prob

Post by ed »

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?
About that stereo

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

Re: odd excel prob

Post by Abdul Alhazred »

Convert the dates to "string" before using the "right" function.
Image "If I turn in a sicko, will I get a reward?"

"Yes! A BIG REWARD!" ====> Click here to turn in a sicko
The arc of the moral universe bends towards chaos.
People who believe God or History are on their side provide the chaos.

User avatar
Posts: 24118
Joined: Tue Jun 29, 2004 12:40 am
Location: New Port Richey, FL

Re: odd excel prob

Post by gnome »


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


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

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

Re: odd excel prob

Post by Mentat »

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?