Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century

[SeanR87]

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. 

[#2]

@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

[#3]

@Sergei Baklan 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,

[#4]

@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)

[#5]

@Sergei Baklan 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. 

[#6]

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

[#7]

@Sergei Baklan 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. 

[#8]

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

[#9]

@Sergei Baklan 

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?

[#10]

@SeanR87 
As I understood the year is two last digits of the number and it shall be in 21st century. Thus

33 => 2033

23 => 2023

etc

What exactly is wrong?

[#11]

@Sergei Baklan 

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. 

[#12]

@Sergei Baklan Yes that is correct but if you look at the year you have for a few of the values is it wrong. 

[#13]

@SeanR87 

Could you please give the concrete example? With that I will try to correct. I really don't see what is wrong.

[#14]

@Sergei Baklan 

[#15]

@SeanR87 

I see. The number 41422, or 4 14 22 means 4th day on 14th month of year 2022.

14th month of year 2022 is the second month of year 2023. Thus result is 2023-02-04

Or how such number shall be interpreted?

[#16]

@Sergei Baklan Let me check and see if that is correct with the employee. You may be right. I will BRB. 

[#17]

That's MDY format, thus just exchange second and third parameters, with that it'll be correct date.

Thus

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

in