Cellwise Try it free

Dates function

Excel TIMEVALUE function

TIMEVALUE converts a time written as text, such as "17:45" or "4:35 PM", into a decimal fraction of a day: noon is 0.5. Use it when imported times will not add up or sort, then format the result as a time.

=TIMEVALUE(time_text)

Arguments

time_text
Text that looks like a time, in quotes or in a cell. Any date in the text is ignored.

TIMEVALUE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1NameIn (text)Out (text)
2Jo8:05 AM4:35 PM
3Sam9:3017:45
4Ola2026-10-05 07:502026-10-05 15:20

Download this sheet as an Excel practice file (.xlsx)

  1. =TIMEVALUE(B2)

    Result: 0.33680555556 shows as 8:05 AM with the custom h:mm AM/PM format

    Returns about 0.3368, the fraction of a day for 8:05 AM. Format it as a time to read it.

  2. =TIMEVALUE(C2)-TIMEVALUE(B2)

    Result: 0.35416666667 shows as 8:30 with the custom h:mm format

    The length of Jo’s shift: 0.354 of a day, which is 8:30 as a time.

  3. =(TIMEVALUE(C3)-TIMEVALUE(B3))*24

    Result: 8.25

    Multiply by 24 for decimal hours, ready for payroll: Sam worked 8.25 hours.

  4. =TIMEVALUE(B4)

    Result: 0.32638888889 shows as 7:50 AM with the custom h:mm AM/PM format

    The date part is ignored, so only 7:50 AM comes back.

How to use TIMEVALUE

  1. Click the cell next to the text time.
  2. Type =TIMEVALUE( and click the text cell.
  3. Press Enter. You see a decimal such as 0.3368.
  4. Format the cell as a time (Ctrl+1 > Time), then fill down.

Common TIMEVALUE mistakes

#VALUE! on a real time

TIMEVALUE only accepts text. If =ISNUMBER(B2) is TRUE, the cell is already a time and needs no conversion.

Negative hours on night shifts

A shift from 10:00 PM to 6:00 AM gives a negative result. Use =MOD(TIMEVALUE(C2)-TIMEVALUE(B2),1) so it wraps past midnight.

Seeing a decimal

That is the time as a fraction of a day, so 0.5 is noon. Format the cell as a time, or multiply by 24 for hours.

TIMEVALUE FAQ

How do I convert text to time in Excel?

Use =TIMEVALUE(A2) and format the result as a time. =VALUE(A2) works too, and Data > Text to Columns > Finish converts a whole column in place.

How do I extract the time from a date and time in Excel?

For a real date-time use =MOD(A2,1). For text such as "2026-10-05 07:50" use =TIMEVALUE(A2), which ignores the date.

Why does TIMEVALUE return #VALUE!?

The cell already holds a real time, or the text is not a time Excel can read, such as "8h05" or "half past 8".

Related functions

Updated