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 about this 2029 rule and even if I change the value in control panel, it doesn't seem to effect Excel. Now, the article does state "This will modify the way Excel interprets dates only when they are typed into a cell. If you import or programmatically enter a date, the following 2029 rule is always in effect."
https://docs.microsoft.com/en-us/office/troubleshoot/excel/two-digit-year-numbers
I don't know if that is the reason why it isn't working or if I am not using a correct value in Windows 10 for the upper-limit year. I tried 2099 and 2150. If you use 2150 the lower limit value looks correct.
The column in the Excel worksheet has numbers such as
91,133.00 |
30,123.00 |
11,123.00 |
41,422.00 |
110,520.00 |
41,422.00 |
What he is doing is highlighting that column, selecting Text to Columns under the Data tab.
Delimted > Next > Other and he uses / > Date MDY > Clicks Finish. I am assuming Excel is creating the year from the last two numbers before the period which is what he wants. Anyways, is there a way to get it to make numbers above 29 change to 21st century using this method without manually typing?
Thanks for the help.
16 Replies
- SergeiBaklanDiamond Contributor
That's a Windows setting if you are on it
Win+R control international Additional Settings Date and increase 2029 up to desired year
- SeanR87Copper Contributor
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,
- SergeiBaklanDiamond Contributor
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)