Forum Discussion
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...
That's a Windows setting if you are on it
Win+R control international Additional Settings Date and increase 2029 up to desired year
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,
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)
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.
