19

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

4 Answers 4

20

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.

1
  • Both the solutions are working fine..!!! Thanks
    – roman_bull
    Apr 24, 2011 at 6:25
10

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

1
  • Thank you this one worked flawlessly, thanks once again for sharing this!!
    – roman_bull
    Apr 24, 2011 at 6:24
1

I had the same problem and I did just one simple thing: =R1*1 in S1, with format selected as Time, and it worked. No complicated code or anything else was needed.

1
  • Simple is good - this worked for me too. Thanks!
    – akaAlso
    Sep 10, 2017 at 20:31
-4

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.

1
  • 1
    Google-links are frowned upon. Your results will be different from that of others.
    – Sathyajith Bhat
    Apr 23, 2011 at 12:15

You must log in to answer this question.

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