Excel TIMEVALUE Function: Convert Text Into a Real Time
Converts a time written as text into a real time value.
TIMEVALUE is to times what VALUE is to numbers and DATEVALUE is to dates: it converts something that looks right and behaves wrong. A column of times imported as text will not subtract, will not sum, and will not sort properly, and TIMEVALUE is the fix.
The symptom is the same as with text numbers. Subtracting one text time from another gives #VALUE!, and a total across a column of them comes out as zero. The alignment gives it away — real times sit right in their cells, text sits left.
It also does something DATEVALUE's counterpart does not: given a full timestamp as text, it returns only the time part, discarding the date. That makes the pair TIMEVALUE and DATEVALUE the standard way of splitting an imported timestamp into two usable columns.
Syntax
=TIMEVALUE(time_text)Arguments
time_textRequired- Text in a time format Excel recognises — "08:15", "8:15 PM", or a full timestamp whose date part is ignored.
The example data
Headers in row 1, data in A2:D5. Columns B and C hold real times.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Staff | Clock in | Clock out | Break (mins) |
| 2 | Alice Moreau | 08:15 | 16:45 | 30 |
| 3 | Bruno Santos | 13:00 | 21:30 | 45 |
| 4 | Chen Wei | 22:00 | 06:00 | 60 |
| 5 | Dana Okafor | 09:30 | 14:00 | 0 |
Worked examples
=TIMEVALUE("08:15")Result: 0.34375
The underlying fraction of a day. Format the cell as a time and it displays as 08:15.
=TIMEVALUE("16:45") - TIMEVALUE("08:15")Result: 08:30
Subtracting two converted times. Doing this on the raw text would give #VALUE!.
=TIMEVALUE("2024-03-15 14:30")Result: 14:30
The date part is discarded, which is how you strip a timestamp down to its time.
=TIMEVALUE("8:15 PM")Result: 20:15
12-hour text with a meridiem is understood and converted to 24-hour.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The text is not in a format Excel recognises for the current locale — a stray space, or a separator it does not expect.
How to fix it: Wrap it in TRIM first. If the source uses a different convention, SUBSTITUTE the separator before converting.
Result shows as 0.34375
Why it happens: The cell is formatted as General and a time is a fraction of a day.
How to fix it: Format the cell as a time. The value is already right.
Applied to a real time it errors
Why it happens: TIMEVALUE expects text. Given an actual time value it has nothing to convert.
How to fix it: Only apply it to text. Check with ISTEXT if a column is mixed.
The date disappeared
Why it happens: TIMEVALUE deliberately returns only the time portion.
How to fix it: Use DATEVALUE on the same text for the date, into a separate column.
Tips worth knowing
- TIMEVALUE for the time and DATEVALUE for the date splits an imported timestamp into two columns in one pass.
- As with VALUE, the fastest bulk fix is Data → Text to Columns → Finish over the whole column.
- =B2 - INT(B2) strips the date off a real timestamp; TIMEVALUE is for when it is still text.
- Check whether you have a problem at all with =ISTEXT(B2) before writing any conversion.
Frequently asked questions
Why will my times not subtract?
They are text rather than time values. Subtracting text gives #VALUE!, and summing it gives zero. Convert with TIMEVALUE, or select the column and run Data → Text to Columns → Finish to re-parse everything in place.
How do I split a timestamp into date and time?
Use DATEVALUE for one column and TIMEVALUE for the other, both pointed at the same text. If the timestamp is already a real value rather than text, =INT(A2) gives the date and =A2-INT(A2) gives the time.
What is the difference between TIMEVALUE and TIME?
TIME builds a time from three numbers you supply. TIMEVALUE converts text that already looks like a time. Use TIME when assembling from parts and TIMEVALUE when repairing an import.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.