User Profile
SeanR87
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century
SergeiBaklan Thanks for your help. I really appreciate it. Although it isn't working, an effort was made. It is my fault. To be honest, I told him hours ago there was no way to get the day the way he was trying. So, it isn't a big deal. If he has to manually modify the dates that are switched to 20th century, that will be fine.Re: Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century
SergeiBaklan Thank you. I actually found a document that explains how you get the date from the number so the fomula makes a bit more sense, however, if you look at what you attached, the year is wrong. I think its because the number grows or shrinks depending on how big it is?Re: Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century
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.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.
