TIMEVALUE
Converts a time stored as text into a serial number that Excel recognizes as a time. Useful when times come from imports or external systems as text strings.
TIMEVALUE(time_text)Arguments
time_textA text string in any Excel time format, e.g. "6:45 PM" or "18:45:00".
=TIMEVALUE("6:45 PM")Returns the serial fraction representing 18:45
=TIMEVALUE("22:30")Returns the serial number for 10:30 PM
=TIMEVALUE(A1)Converts the text time in A1 to a numeric time
- •Result is a fraction between 0 (midnight) and 1 (next midnight)
- •Format the result as time to display it readably
- •If the cell already contains a real time (not text), TIMEVALUE is unnecessary
- •Returns #VALUE! if the text cannot be parsed as a valid time
- •Applying TIMEVALUE to a cell that's already a real time value, which returns a #VALUE! error since TIMEVALUE expects text as input, not an existing time
- •Not accounting for regional time format differences - a text string like "3:00" might be interpreted differently depending on whether the system expects a 12-hour or 24-hour format
- •Forgetting that TIMEVALUE strips any date component from the text, returning only the time's fractional value even if the original text included a full date and time
DATEVALUEPerforms the same kind of conversion but for the date portion of a text string rather than the time.TIMEBuilds a time value from separate hour, minute, and second numbers, rather than parsing a text string.TEXTThe reverse operation in spirit - converts a time value into formatted text instead of parsing text into a time.Why does TIMEVALUE return a #VALUE! error?
The cell probably already contains a real time value, not text - TIMEVALUE only works on text strings that look like times. Applying it to an actual time causes the error.
Does TIMEVALUE keep the date if the text includes one?
No, it only returns the time's fractional value. Use DATEVALUE separately if you also need to extract the date portion.
Does TIMEVALUE handle both 12-hour and 24-hour text formats?
It generally handles both, like "3:00 PM" or "15:00", but ambiguous or unusual formats can be misread depending on your system's regional settings.
Need to translate a formula using TIMEVALUE?
Use our translator to convert your complete formula