CODEWITHSUNDEEP
excelcsv
Oo7
Oo7

Reputation: 51

Convert date from csv to Excel 2016

It's an easy one but I can't figure it out yet, I've tried searching here but didn't find anything.

I have a csv file, and one of the columns represents the date as:

2015-12-04 11:48:04:017

I want Excel to recognize it as date (not just text). How can I do this ?

Thank you !

Upvotes: 0

Views: 211

Answers (1)

Code Different
Code Different

Reputation: 93151

The problem is that your string is not properly formatted for date-time value. If the last comma were a dot, Excel would have recognized it as a date-time value:

2015-12-04 11:48:04:017     -- bad
2015-12-04 11:48:04.017     -- good

If you can use a text editor or a script to correct the CSV file, that would be great. Failing that, you can use a formula in Excel to convert it:

=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))
=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8)&"."&MID(A1,21,100))

This assume the cell A1 contains the text you want to convert. The first formula ignores the fractional seconds to make things shorter. The second formula includes the fractional second.

Upvotes: 1

Related Questions