Nov 06 2021 10:03 AM
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.
Nov 06 2021 01:10 PM
SolutionThe 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.
Nov 06 2021 01:12 PM
@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.
Nov 06 2021 05:45 PM
Nov 06 2021 01:10 PM
SolutionThe 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.