Excel Forum

BLACK FRIDAY SALE 20% OFF ALL COURSES

Zip Code Type Dates are converting into Future Dates

P B

Member

Members

Forum Posts: 38

Member Since:
March 8, 2021

Offline

November 5, 2021 - 5:07 am

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,

Mynda Treacy

Admin

Forum Posts: 4647

Member Since:
July 16, 2010

Offline

November 5, 2021 - 9:28 am

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

P B

Member

Members

Forum Posts: 38

Member Since:
March 8, 2021

Offline

November 5, 2021 - 9:28 pm

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,

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 886

Member Since:
December 7, 2016

Offline

November 6, 2021 - 1:47 am

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

P B

Member

Members

Forum Posts: 38

Member Since:
March 8, 2021

Offline

November 6, 2021 - 9:23 pm

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,