Date/Time format in CSV file/ excel

Question

with date/time formats as follows:

Date/Time
02-05-2018 07:45
02-05-2018 07:50
1/17/2018 2:15
1/17/2018 2:30
1/17/2018 2:45

Above "02-05-2018" is suppose to 5th of feb, but its taking 2nd of May.

Basically the format is as below :

02-05-2018 07:45 - dd-mm-yyyy hh:mm with actual value 43222.32292 which is a text format i beleive

Now I need to all such entries to convert to normal date/time format : mm-dd-yyyy hh:mm

Please let me know, tried few approaches but no luck

Solution 1

The problem is that the date format of the file, and the short date format of your computer in Windows Regional settings, are different.

Do not OPEN the file. Rather you can IMPORT the file. When you do that, the data import wizard will open and you can define the format of the date (MDY) in the incoming csv file.

Exactly where to find the IMPORT method will differ depending on your version of Excel. And you may have to split the time from the date for the import/conversion process, but you can always recombine by adding the two cells later.

Solution 2

It looks like you're opening a CSV file in Excel. The CSV file is a text file but when you open it in Excel, it will convert the text to a date value. It will use the system date format to guess whether mm-dd or dd-mm.

One solutions would be to set the system date format to mm-dd-yyyy. Then open the file in Excel. Then you can change the system date format back. Probably not the best option.

Or you can use a formula to convert/swap day/month. =IFERROR(IF(ISTEXT(A2),DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2)),DATE(YEAR(A2),DAY(A2),MONTH(A2))),"") So if the date didn't convert and shows as text, this formula will convert to date. If it's already showing as a date, this formula will assume that the day/month are reversed and will swap them.

In Excel be sure to use cell formatting like this: mm/dd/yyyy or mm-dd-yyyy to show your date correctly.

Comments

[D_Bester]

"actual value 43222.32292" is a number (not text) which is date May 2, 2018.

[D_Bester]

The CSV file holds a date as text "02-05-2018". When opened in Excel it's assuming the correct date format is dd-mm-yyyy. So it's thinking 2nd May 2018 with the numeric value of 43222.

[Admin]

Duplicate of Excel VBA - Convert Text to Date?.

[ImaginaryHuman072889]

Wow, neat trick. Never knew about this. Just was playing around with this and it allows you to also choose which delimiters are used to separate columns. Very useful! +1

[Ron Rosenfeld]

@ImaginaryHuman072889 The option has been around for a long time (20+years) and recently updated in Office 365. But it is not always easy to locate.