Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
Thanks! (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 Jun 21 '10 at 11:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.