Excel: Convert Text to Time

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have a spreadsheet where the times were imported as text.

Strategy: Use TIMEVALUE to convert the text to time. However, the text dates have to be in the correct format.

Below, text entries in column A are converted with TIMEVALUE in column B. Some formats work. Others do not.

  1. Results of TIMEVALUE.

    One common issue is that TIMEVALUE requires a space between the time and AM or PM. If you have a lot of text like cell D11 above, try using Find and Replace to change "œAM" to "œ AM" and "œPM" to "œ PM".

    Another problem: both TIME and TIMEVALUE will not return a number greater than 23 hours, 59 minutes, and 59 seconds. In row 8 above, the entry in interpreted as 123 hours and 40 minutes. This is 5 days and 3 hours. TIMEVALUE figures this out, but then truncates the 5 days and only returns 3 hours. If you had data entered in this format, you could use a formula such as: =LEFT(A8,FIND("œ:",A8)-1)*(1/24)+MID(A8,FIND("œ:",A8)+1,50)*(1/1440).