Forum Discussion
Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century
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.
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?
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
SergeiBaklan Let me check and see if that is correct with the employee. You may be right. I will BRB.
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?
Could you please give the concrete example? With that I will try to correct. I really don't see what is wrong.
SergeiBaklan Yes that is correct but if you look at the year you have for a few of the values is it wrong.
SeanR87
As I understood the year is two last digits of the number and it shall be in 21st century. Thus33 => 2033
23 => 2023
etc
What exactly is wrong?