What TIMEVALUE does
TIMEVALUE converts text such as 09:30 into a numeric Excel time. Use it when imported clock times are stored as text and you need to calculate with them. Apply a time number format to make the result readable as hours and minutes.
Excel stores a time as a fraction of a day. Noon is 0.5, and midnight is 0. If the text also contains a date that Excel recognizes, TIMEVALUE keeps only the time portion.
Practical examples
Convert an imported morning time
Suppose A2 contains the text 09:30. To reproduce it manually, type '09:30. The leading apostrophe keeps the entry as text and does not appear in the cell.
Enter this formula in B2.
=TIMEVALUE(A2)The result is approximately 0.3958333333, which is 9.5 hours divided by 24. Apply the custom number format h:mm to B2 to display 9:30. The result remains numeric, so you can use it in a calculation.
Convert an evening time with AM/PM
Enter the text 6:45 PM in A3, using a leading apostrophe if needed. This example assumes your Excel environment recognizes English AM/PM notation.
=TIMEVALUE(A3)The result is 0.78125. With h:mm formatting, it displays 18:45. An AM/PM time format displays 6:45 PM.
Clean ordinary spaces around a time
Suppose A4 contains the text 09:30, with an ordinary space at each end. Use TRIM to clean those spaces before converting the text.
=TIMEVALUE(TRIM(A4))The result is approximately 0.3958333333, displayed as 9:30 with h:mm formatting. TRIM handles ordinary spaces. It does not remove every invisible character, including nonbreaking spaces copied from some web pages.
Common mistakes and notes
Check the text when you get #VALUE!
TIMEVALUE returns #VALUE! when it cannot interpret the input text as a time. Check separators, spaces, and AM/PM notation against a clock time Excel recognizes locally. Regional settings affect how imported text is read.
Format the converted result
Applying a Time format to the original text cell leaves it as text. Convert the value first, then format the numeric result. A decimal in the result cell can mean the conversion worked and the cell still has General formatting.
Check whether the source is already numeric
Use ISNUMBER to check the stored type. A numeric Excel time can be formatted directly. For a numeric date and time, follow the time extraction guide to separate its time portion. A time-only format hides the date while leaving it in the stored value.
Keep elapsed durations on their own calculation path
TIMEVALUE reads clock-time text within a day. For an elapsed amount such as 27 hours and 30 minutes, preserve the full duration. If the source provides 27.5 decimal hours, divide by 24 and apply [h]:mm to display 27:30. The decimal-hours conversion guide explains that calculation.
Preserve the original text if you need its date
TIMEVALUE ignores a recognized date portion in its text input. Keep the source column when you still need the complete timestamp. DATEVALUE can convert recognized date text separately.