1
I am trying to import a text file (tab-separated columns) into excel. The log file uses MM/DD/YYYY HH:MM:SS,mmmm (where mmmm is milliseconds), which excel treats as string.
How can I get excel to recognize it as a date, so I can sue it as X axis for charts, reformat it etc?
(System is W7 Ultimate set to use an English/US UI, Excel is German)
peterchen
Posted 2010-06-21T08:43:49.440
Reputation: 1 602
2
You can add a new column after import and then convert the strings into an Excel datetimes with:
=DATE((MID(A1,7,4)),(MID(A1,1,2)),(MID(A1,4,2))) +TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
You can also add the fractional part if you need that resolution with:
=DATE((MID(A1,7,4)),(MID(A1,1,2)),(MID(A1,4,2))) +TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2)) +VALUE(MID(A1,21,4))/(86400*10000)
These examples assume your string is in cell A1.
Mike Fitzpatrick
Posted 2010-06-21T08:43:49.440
Reputation: 15 062
1Thanks! (Of course, I had to replace DATE with DATUM, MID with TEIL, and the commas with semicolons - whoever thought of localizing formulas deserves a slap on the forhead. With a sledge hammer...) ---- can't vote you up yet, unfortunately. – peterchen – 2010-06-21T11:51:24.813