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]

Most likely the cells with the dates are formatted as Text numeric format. To manually change those, highlight the cells, press Ctrl-1, Choose the Number Tab, choose Date, choose the date format you want. Now to change those dates into values. With those dates still selected, choose DATA > Text to Columns > Finish (Don't do anything else in that dialog).

 

[Chris1973]

Hi Jeffrey

Thanks for the answer. I have done exactly as per your answer, it does change the date format, but the year still change to 2022 ot 2013 or 2030, So the date and month is correct but the year is incorrect.

What is strange if i mail this to someone does it display correctly with them, just with me that the dates are incorrect

 

[Jeffrey Mahoney]

I have worked with a ton of CSV files from many sources. Can you share a screenshot of the raw CSV data. Most likely approach is you're going to need to create a special import format so that it brings those years in properly.

 

[Chris1973]

This is how the file is safe when downloaded. If i open it will it be as follow

 

[RoryA]

Open it in a text editor like Notepad, not Excel, and see what format the dates are in.

 

[Jeffrey Mahoney]

So the data is in this format: 23 April 2013
How did you expect the year to be other than 2013?
Is this a view in a spreadsheet or a text editor?
Is the first number a year, as in 2023?

 

[Chris1973]

The dates is suppose to be 23 April 2023.
All the data on the sheet is for 2023, as explained does the day and month display correct, but the year is completely incorrect, i have no idea how this year get generated, but as mentioned if email this to someone else does it actually display correctly

 

[Jeffrey Mahoney]

Please show the CSV from a text editor. It may become apparent on how to import the data correctly.

 

[Chris1973]

Jeffrey, with all honesty, i have no idea how to do that as this is how i copy this and open on my laptop. I don't do anything else