Forum Discussion

SeanR87's avatar
SeanR87
Copper Contributor
Jul 03, 2020

Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century

Hello,   I have an employee who is having an issue with converting numbers in an Excel column to dates. Any number ending above 29 is converted to 20th century i.e 33 is 1933 and so forth. I read a...
excel
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 03, 2020

SeanR87 
As I understood the year is two last digits of the number and it shall be in 21st century. Thus

33 => 2033

23 => 2023

etc

 

What exactly is wrong?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 03, 2020

    That's MDY format, thus just exchange second and third parameters, with that it'll be correct date.

    Thus

    =DATE(20&RIGHT(AB1,2),LEFT(AB1,LEN(AB1)-4),MID(AB1,LEN(AB1)-3,2))
or
=DATE(
   VALUE("20"&RIGHT(AB1,2)),
   VALUE(LEFT(AB1,LEN(AB1)-4)),
   VALUE(MID(AB1,LEN(AB1)-3,2))
)

    in

     

  • SeanR87's avatar
    SeanR87
    Copper Contributor
    Jul 03, 2020

    SergeiBaklan Let me check and see if that is correct with the employee. You may be right. I will BRB. 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 03, 2020

    SeanR87 

    I see. The number 41422, or 4 14 22 means 4th day on 14th month of year 2022.

    14th month of year 2022 is the second month of year 2023. Thus result is 2023-02-04

     

    Or how such number shall be interpreted?

Resources