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 have a column that is text formatted like 20110408_2041. How can I format it to date/time format?

share|improve this question

3 Answers 3

up vote 4 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:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0)

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:

Example

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
1  
Google-links are frowned upon. Your results will be different from that of others. –  Sathya Apr 23 '11 at 12:15

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.