Forum Discussion
Excel 2029 rule - How to stop it from calculating numbers above 29 to the 20th century
SergeiBaklan 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.
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.
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.
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.
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?
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?
