Incorrect date

[Chris1973]

Hi Team

I have a bit of a issue,
I need to download a file from a machine, this file is in csv format. After saving the file to xlsx does the following proble occur.

My dates are completly in the wrong format

2013/04/23​	08:31​
2013/04/23​	09:28​
2009/05/23​	14:55​
2022/09/23​	11:11​
2022/09/23​	13:48​
2029/09/23​	10:53​
2009/10/23​	12:57​
2002/11/23​	16:36​
2017/11/23​	14:08​
2017/11/23​	15:42​
2017/11/23​	15:50​
1930/11/23​	14:39​
1930/11/23​	14:48​
1930/11/23​	15:01​
1930/11/23​	15:20​
1930/11/23​	15:42​
2007/12/23​	09:52​
2007/12/23​	13:59​
2020/12/23​	13:25​

As you can see here the date ranges from 2013 to 2030, What can i do to get the date displayed correctly

Thanks

 

[Jeffrey Mahoney]

right click on the file in a Windows Explorer window and choose Open With > Notepad

IF Notepad is not an option then select Choose Another App and then choose notepad

 

[Chris1973]

 

[Jeffrey Mahoney]

Well, Excel is not wrong in interpreting the dates. I can't even suggest what those 4 digit numbers could be other than years. Was the intention to only have Month and Year? If in fact the year is always 2023, then no telling if the first two or last two digits is a day of the month.

I'm suspecting that the device or reporting tool you got this data from is messed up.

 

[Jeffrey Mahoney]

Are those expiration dates for vehicle license plates? If so then the reporting day of the month could very well be the 23rd of each month and the years are correct.

 

[Chris1973]

Thanks so much. My biggest concern will be then why does it display correctly when mailing to someone

Any case i do thank you for your help and time

 

[Jeffrey Mahoney]

Thanks so much. My biggest concern will be then why does it display correctly when mailing to someone

When you say correctly, do you have an example of what others see?

 

[Chris1973]

I don't have the sample with me but it was confirmed today it show as follow

In excel does it show the following by me

2013/04/23

My colleague see the following
2023/04/23

So for one or other reason unknown to me does it change my year from 2023 to 2013 or from 2023 to 2030

 

[Jeffrey Mahoney]

Well, to be honest, Excel will not interpret 2013 to 2023. Somebody is forcing it to be 2023. If you know, in fact, that it is 2023, you could force it manually with a formula to read the Month & Day from the imported data
=DATE(Year, Month, Day)

 

[Chris1973]

Kan you please just advise how i need to enter this

Thanks

 

[awoohaw]

My suggestion would be to import that field as TEXT in the import wizard.
Then use the TEXT to COLUMNS and choose DATE, YMD as the format type.
Sorry, nevermind on that. I wasn't thinking.

But I would definitely contact the source of your data and tell them they are exporting days as years to you.
They may not believe you, send them a copy of the screen shot of notepad (Or tell them to open in notepad).

I bet their system of record has two digit years. And they pad a "20" to the front of what they think the year is.