Convert Text to Time

July 13, 2022 - by Bill Jelen

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.

The TIMEVALUE function can interpret a lot of different text entries for time. There are a few that cause problems: If the minutes or seconds is 61 or higher. Or, if there is no space between the time and AM or PM. — Figure 570. 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).

This article is an excerpt from Power Excel With MrExcel

Title photo by Ricardo Gomez Angel on Unsplash