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
SeanR87's avatar
SeanR87
Copper Contributor
Jul 03, 2020

SergeiBaklan I have changed that setting and it doesn't effect anything as I stated in my post. I have tried multiple numbers including 2150, 2099, 3000, 9999. 

 

Regards,

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 03, 2020

SeanR87 

You are right, sorry. When perhaps macro or formula like

=DATE(20&RIGHT(A1,2),MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))

drag it down (or select the range and Ctrl+D)

 

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

    SergeiBaklan I am still missing something. Numbers start on AB2. 

     

    =DATE(20&RIGHT(AB2,2),MID(AB2,LEN(AB2)-3,2),LEFT(AB2,LEN(AB2)-4))

     

    Obviously, you made that not knowing how many columns etc I had which is my fault and my fault for not understanding. 

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

    SergeiBaklan Thank you for that! I am bad at formulas etc. In the document he is working in. The column is labeled AB. So I can just change your values from A to AB? There are thousands of rows so. 

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

      SeanR87 No,  that didn't work. I think the first part needs to be modified. (20&right) ?

       

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

       

      I just get a #VALUE! when entered. 

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

        SeanR87 

        It shall work, but you may try more formal formula

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

        After that stay on first cell with the formula, type in named box entire range reference

        Enter (range will be selected) and Ctrl+D (formula will be populated on entire range)

        Please open attached file to check how the formula works.

         

Resources