if statement to get year from 2 digits...

jaffamuffin

New member
Joined
Sep 5, 2013
Messages
6
Reaction score
0
Points
0
Hi All

Hope you can help

I have this :
S
T
U
31/12
=RIGHT(S2,2)
=IF(RIGHT(S2,2)<13,"20"&RIGHT(S2,2),"19"&RIGHT(S2,2))
31/12
=RIGHT(S3,2)
=IF(T2<13,"20"&T2,"19"&T2)




But it seems to evaluate to false giving me 1912 instead of 2012. If I just type 12 into the formula rather than extracting it it give me the right result of 2012.

What am I missing ?

Cheers all
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
=RIGHT(S2,2)*1 to get a number instead of a string
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

This is probably not the most effective way to get what you're looking for but I think it will work in a pinch. It is returning a number of days and not a year.

=TEXT((("20"&RIGHT(S2,2)+0)-1900)*365.3,"YYYY")
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
If your working in days, perhaps :
=TEXT((("20"&RIGHT(S2,2)+0)-1900)*365.24219879+2,"DD/MMM/YYYY") for accuracy !



 

jaffamuffin

New member
Joined
Sep 5, 2013
Messages
6
Reaction score
0
Points
0
Thanks all What I did in the end was this :

Code:
=IF(S2="","",(IF(RIGHT(S2,2)<"13","20"&RIGHT(S2,2),"19"&RIGHT(S2,2))))

I think using the speech marks made it do a lexigraphical compare rather than a numeric, but either way it compares properly and gave me the right result.
 
Top