Forum Discussion
"Date and time" format issue after splitting a csv file into different columns
Hello,
I have a csv file that I can open with Excel. This file has data with a "date and time" format which is the same for every row. Then, when splitting the text from the csv file into different columns, the "date and time" format is converted differently for different rows. For example one row may have a "date and time" format of "01/13/2021 17:01" and the other may have it as "01/13/2021 5:01 PM".
Also I tried to change the "Date and time" format of the entire column but the modification is not taken into account.
How can I split my data without having that impact on the date and time format ? I would like to have an unique homogeneous format.
Please find below attached screenshots with an ordered name (from 1 to 5) for a better understanding of the issue.
Let me know if further information is necessary.
Thank you.
The dates are in US format m/d/yy, but Excel has tried to interpret them as d/m/yy.
12/28/20 is not a valid date in d/m/yy format, so it has been left as text (tell-tale sign: it's left-aligned).
Others could be interpreted as d/m/yy, but day and month come out switched.
One workaround is to switch your system date format to m/d/yy temporarily before importing the data, then switching back to the original setting afterwards.
3 Replies
ErnestoCB In step 3 of 3, select Date MJA for the date column as it seems that the dates are in a month/day/year format.
The dates are in US format m/d/yy, but Excel has tried to interpret them as d/m/yy.
12/28/20 is not a valid date in d/m/yy format, so it has been left as text (tell-tale sign: it's left-aligned).
Others could be interpreted as d/m/yy, but day and month come out switched.
One workaround is to switch your system date format to m/d/yy temporarily before importing the data, then switching back to the original setting afterwards.
- Thanks a lot for your reply it worked for me 🙂
It worked while changing the format using the "Parameters" application, and not in the control panel.
If someone needs it, useful information can be found here in order to change the date format with the correct application: https://access-excel.tips/how-to-change-default-excel-date-format/
