Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a column that is text formatted like 20110408_2041. How can I format it to date/time format?

share|improve this question
up vote 8 down vote accepted

Assuming that your string is coded YYYMMDD_HHMM for Year, Month, Day, Hour, Minute, then you can construct an Excel formula to generate a cell in date/time format.

If the string is in cell A1, then this formula ought to do it:


You will need to format the cell properly to see the time portion. This is tested in LibreOffice calc, but Excel appears to have the same functions.

share|improve this answer
Both the solutions are working fine..!!! Thanks – roman_bull Apr 24 '11 at 6:25

Assuming the whole format is YYYYMMDD_HHMM and that the first such value is in cell A1, you can use the following formula in B1:

=DATEVALUE(MID(A1,7,2) & "/" & MID(A1,5,2) & "/" & MID(A1,1,4)) + TIMEVALUE(MID(A1,10,2) & ":" & MID(A1,12,2))

This creates a date serial number based on DD/MM/YYYY using the MID function to gather each part, and the DATEVALUE function to return the serial number. It then adds to this the time serial number which uses the MID function in a similar way to gather the hours and minutes.

You can then format the cell as a Date format, Time format, or a custom format of say dd/mm/yyyy hh:mm to see the end result. Just right click the cell, choose Format Cells then configure similar to the screenshot below:

Format cells dialogue

Here's a screenshot of the end result:


share|improve this answer
Thank you this one worked flawlessly, thanks once again for sharing this!! – roman_bull Apr 24 '11 at 6:24

You can do that by writing a small script (with C# for example) that reads the strings from the column converts them to date time format and then saves them back to the Excel column.

Sorry, I don't know if you can do that directly from Excel.

share|improve this answer
Google-links are frowned upon. Your results will be different from that of others. – Sathya Apr 23 '11 at 12:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .