Forum Discussion

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

Diamond Contributor

Jul 03, 2020

SeanR87

That's a Windows setting if you are on it

Win+R control international Additional Settings Date and increase 2029 up to desired year

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