Monday, 6 May 2019

import - Make excel recognize foreign date format


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)



Answer



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.


No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...