odd excel prob

The war between wetware and hardware.
User avatar
ed
Posts: 32422
Joined: Tue Jun 08, 2004 11:52 pm
Title: Trilobite of the Florida swamp
Has thanked: 418 times
Been thanked: 697 times

odd excel prob

Post by ed » Mon Jan 22, 2018 12:13 pm

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?
- new minimalist ethos -

User avatar
Abdul Alhazred
Posts: 69399
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 2773 times
Been thanked: 1062 times

Re: odd excel prob

Post by Abdul Alhazred » Mon Jan 22, 2018 12:50 pm

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
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

User avatar
gnome
Posts: 21763
Joined: Tue Jun 29, 2004 12:40 am
Location: New Port Richey, FL
Has thanked: 336 times
Been thanked: 341 times

Re: odd excel prob

Post by gnome » Mon Jan 22, 2018 1:09 pm

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

User avatar
Mentat
Posts: 10271
Joined: Tue Nov 13, 2007 11:00 pm
Location: Hangar 18
Has thanked: 37 times
Been thanked: 124 times

Re: odd excel prob

Post by Mentat » Mon Jan 22, 2018 2:49 pm

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?