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