Excel TIMEVALUE Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 87 of 105 · Full Excel course

The Excel TIMEVALUE function converts a time written as text, such as “2:30 PM” or “18:45:10”, into the decimal serial number Excel uses for real times. The result is a fraction of a day between 0 and 0.99999, which can be formatted as a time, added to dates and used in duration calculations. TIMEVALUE is the fix for imported time columns that align left and refuse to sum.

Table of Contents

Syntax

=TIMEVALUE(time_text)

Arguments

Argument	Required / Optional	Meaning
time_text	Required	Text in any time format Excel recognises, such as “6:45 PM”, “18:45” or “04/09/2026 18:45”, or a reference to a cell containing such text. Any date information in the text is ignored.

How TIMEVALUE works

TIMEVALUE parses the text and returns hours, minutes and seconds as a fraction of 24 hours: =TIMEVALUE("12:00 PM") returns 0.5, =TIMEVALUE("18:00") returns 0.75 and =TIMEVALUE("6:45:30 AM") returns 0.28160. If the text also contains a date, only the time part is used. A cell in General format shows the decimal; apply a time format to see 18:00. Text that is not a valid time returns #VALUE!, and so does a cell that already contains a real time number, because TIMEVALUE accepts text only.

Step-by-step example: make a text time column add up

Column A holds call durations exported as text, such as 0:07:45. They align left and =SUM(A2:A50) returns 0.
In B2 enter =TIMEVALUE(A2). The result is 0.005382.
Format column B with [h]:mm:ss. B2 now shows 0:07:45 and aligns right.
Fill down and sum column B. The total is correct, and the square brackets allow it to exceed 24 hours.

Practical use cases

1. Full timestamp from separate text date and time

=DATEVALUE(A2)+TIMEVALUE(B2)

2. Extract the time from a text timestamp

=TIMEVALUE(A2)

“04/09/2026 15:30” returns 0.6458, the time only, ready for HOUR or comparison.

3. Times written with a full stop

=TIMEVALUE(SUBSTITUTE(A2,".",":"))

4. Four-digit text such as 1430

=TIMEVALUE(LEFT(A2,2)&":"&RIGHT(A2,2))

5. Safe conversion of a mixed column

=IF(ISNUMBER(A2),A2,IFERROR(TIMEVALUE(A2),""))

Real times pass through, text times are converted and unreadable entries become blank.

Common mistakes and tips

#VALUE! on a real time: the cell already holds a number. Use the ISNUMBER test above or skip conversion.
Result shows a decimal: apply a time format; the conversion is correct.
Regional formats: “6.45 PM” or “18h45” is not recognised on most systems. Normalise separators with SUBSTITUTE first.
Durations over 24 hours as text: “26:30” is converted to 02:30 because TIMEVALUE drops whole days. Use VALUE or arithmetic for long durations.
Bulk conversion: Data > Text to Columns, or Power Query’s Change Type to Time, converts a whole column without formulas.

TIMEVALUE compared with VALUE, TIME and DATEVALUE

VALUE converts any numeric text, including times, so =VALUE("18:00") also returns 0.75; TIMEVALUE is clearer about intent and guarantees that any date in the text is dropped. TIME builds a time from numeric hour, minute and second arguments and is the better choice when the components are already numbers or extracted with LEFT and RIGHT. DATEVALUE is the date counterpart: it keeps the date and drops the time, so DATEVALUE plus TIMEVALUE reconstructs a full timestamp from one text string. In Excel 365 arithmetic on text times sometimes coerces them automatically, but SUM, AVERAGE and lookup functions do not, which is why an explicit TIMEVALUE step still matters in reports built from exported data.

Related functions

DATEVALUE: converts text dates to serial numbers.
TIME: builds a time from numeric parts.
VALUE: converts any numeric text.
HOUR: reads the hour from the converted time.
See all lessons in the Excel Formulas course.

Frequently asked questions

Why does TIMEVALUE return #VALUE!?

Either the text is not in a recognisable time format, or the cell already contains a real time number rather than text. Fix the separators or test with ISNUMBER first.