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.
- 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).