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

 

[Chris1973]

Hi 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.""

Can you just clarify this for me please not sure how or where. The rest i can do

 

[awoohaw]

Hi 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.""

Can you just clarify this for me please not sure how or where. The rest i can do

it was a bad suggestion.

What is your system settings date format? (How does your Excel display a date when you just type in =DATE(2023,2,17)

 

[Chris1973]

20/02/2024 day/month/year

 

[awoohaw]

Well, maybe it isn't your system but just what you're receiving. I think the sender is:
1. Has their dates in DD/MM/YY order
2. Trying to send as CCYY/MM/DD
3. But grabbing the wrong components when they PAD with a "20".

What you can do, is try:
1. Import that field as Text (Assuming Column A, data beginning on Row2).
2. You can't do a FIND and REPLACE of all "20" to "" because you'll remove valid days (the 20th of each month from values of 2020)
3. Create a new column and use this formula (which is what @Jeffrey Mahoney was suggesting:
Assuming Dates are in Column A.

=DATE(RIGHT(A2,2)+2000,MID(A2,6,2),MID(A16,3,2))

When that is done paste special those values over themselves and use that as your new date column, or paste values into the date column. Then delete the extra column.
If you're familiar with power query or macros you could do this nearly instantly, but would take some time setting it up.
(My suggestion is to get the sender to fix the file creation process).

 

[Chris1973]

Thanks so much i will try this and revert back

 

[awoohaw]

Thanks so much i will try this and revert back

Your welcome. But, also continue verifying through your entire process. If you then send this file to someone else, make sure they receive the data so that it is processed correctly. (Let them confirm with you, don't assume what you send is how their system processes it).