Results 1 to 6 of 6

Thread: if statement to get year from 2 digits...

  1. #1

    if statement to get year from 2 digits...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    722
    Articles
    0
    Excel Version
    Excel 2010 64bit
    =RIGHT(S2,2)*1 to get a number instead of a string

  3. #3
    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")

  4. #4
    You're welcome.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    791
    Articles
    0
    Excel Version
    2010
    If your working in days, perhaps :
    =TEXT((("20"&RIGHT(S2,2)+0)-1900)*365.24219879+2,"DD/MMM/YYYY") for accuracy !




  6. #6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •