Hello Mynda,
Hope you are doing well? I really need help with this weird issue. The files I get from vendors, send dates like zip codes and sometimes you can't convert them into date format in a normal way. I tried many ways. When I try to use Data> Text to Columns and on Convert text to Column Wizard (3rd Step) I select a date and then also it does not change all the time but after that, if you go back to formatting and try to change it to date it will change the date in Future Dates like 41729 will become 04/17/2029 and 42625 will become 04/26/2025. It is adding twenty in front of the last two digits.
How can zip code format dates (I receive from vendors) be changed properly?
Please advise.
Thanks,
PB
Hi PB,
What date should 41729 and 42625 be? Are these representing date serial numbers or something else? If you can attach an Excel file with your sample data and desired result it will help us to help you.
Mynda
Hello Mynda,
Thank you! I don't know what dates should be since asking them any questions is calling for a lot of trouble. I know surely these are the wrong dates because these are the dates they are supposed to start working. You won't have starting dates in 2029 or 2028. This is insane.
Thanks,
PB
Hello,
If the numbers are Excel date serial numbers then the dates are:
41729 = 2014-03-31 (March 31, 2014)
42625 = 2016-09-12 (September 12, 2016)
Would that be correct?
Br,
Anders
Hello Anders,
Thank you! They come in the Start Date Field, so I don't know when you wrote Excel date serial numbers? Can you please explain how you came to these dates? I just gave 2 examples but 90% of their dates are like this
41729 = 2014-03-31 (March 31, 2014)
42625 = 2016-09-12 (September 12, 2016)
Thanks,
PB
Hi PB,
Dates are stored as 'serial numbers' in Excel. Please read this for explanation
Excel Date and Time - Everything you need to know (myonlinetraininghub.com)
Regards
Phil
