Forum Discussion
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 a...
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.