Excel TIMEVALUE Function

TIMEVALUE converts a time that's stored as text into a decimal number Excel recognizes as an actual time. If a cell holds "9:00 AM" as text, Excel can't add it to another time or use it in a duration calculation. TIMEVALUE fixes that by returning the underlying serial number, in this case 0.375, which Excel then formats however you tell it to. Serial numbers are how Excel stores every date and time internally: times are stored as fractions of a day, so noon is 0.5, 6 PM is 0.75, and midnight is 0.

Syntax

=TIMEVALUE(time_text)

Parameter	Required	Description
time_text	Yes	A text string that looks like a time: "9:00 AM", "14:30", "6:30:15 PM". Must be entered as text or come from a cell formatted as text. If the cell already holds a real time value, TIMEVALUE isn't needed.

Basic Example

You've imported a batch of shift-start timestamps from a payroll system, and they landed in column B as plain text, left-aligned and unusable in any time math.

=TIMEVALUE(B2)

// B2  = the text string "8:15 AM"

TIMEVALUE reads "8:15 AM" and returns 0.34375, which is 8 hours and 15 minutes expressed as a fraction of a 24-hour day. Format the result cell as h:mm AM/PM and it displays as 8:15 AM again, except now it's a real number you can subtract, sum, or feed into a duration formula.

How TIMEVALUE Works

It returns a number, not a formatted time

TIMEVALUE always returns a decimal between 0 and 0.999988426, never text and never a visibly formatted time. If the result cell shows something like 0.375 instead of 9:00 AM, the value is correct. You just need to apply a time format to the cell, not fix the formula.

It ignores the date portion of a text string

If time_text includes a date, like "3/15/2026 9:00 AM", TIMEVALUE extracts only the time and discards the date. The result is still 0.375, identical to what you'd get from "9:00 AM" alone. To capture the date too, use DATEVALUE on the same string separately.

It accepts both 12-hour and 24-hour formats

"9:00 AM", "09:00", and "9:00" (interpreted based on your system locale) all work. Seconds are optional: "6:30:15 PM" returns a value that includes the seconds component. Leaving out AM/PM on an ambiguous time like "9:00" defaults to a 24-hour reading in most regional settings, so double-check results against a known value the first time you use it on new data.

It requires a recognizable time pattern

TIMEVALUE parses the string against your system's regional time settings, not a fixed universal format. "9.00 AM" with a period instead of a colon will fail in most locales, even though a human reads it the same way.

Common Use Cases

Cleaning imported timestamps

Payroll exports, POS systems, and API pulls frequently dump times as text with inconsistent formatting.

=TIMEVALUE(C2)   // converts imported text "17:45" into 0.739583...

Calculating elapsed time between two text timestamps

A helpdesk log stores ticket opened and closed times as text strings, and you need the duration in hours.

=(TIMEVALUE(E2) - TIMEVALUE(D2)) * 24

// D2 = ticket opened, stored as text "9:12 AM"
// E2 = ticket closed, stored as text "11:47 AM"
// *24 converts the day-fraction result into hours

Building a full datetime from separate date and time text

A legacy export splits date and time into two text columns, and you need one combined, sortable datetime value.

=DATEVALUE(F2) + TIMEVALUE(G2)

// F2 = "3/15/2026" as text
// G2 = "9:00 AM" as text
// Format the result cell as a date-time custom format to display both

Extracting hours or minutes from a text time

You want the hour component alone, for grouping shift-start times into buckets on a dashboard.

=HOUR(TIMEVALUE(H2))   // returns 9 from the text string "9:00 AM"

Handling Errors

TIMEVALUE throws #VALUE! when it can't parse the string as a time.

Common causes of #VALUE!:

• The text doesn't match a recognizable time pattern for your regional settings ("9.00am" instead of "9:00 AM")
• The cell actually contains a real time value already, and TIMEVALUE is being applied on top of it unnecessarily, with an extra character or space breaking the parse
• There's a leading or trailing space in the imported text (common with CSV exports)
• The string mixes date and time in a format Excel doesn't expect, rather than a clean time-only string

=IFERROR(TIMEVALUE(B2), "Check format")
=IFNA(TIMEVALUE(B2), "")   // only useful if TIMEVALUE is nested inside a lookup; TIMEVALUE itself never returns #N/A directly

Notes & Gotchas

Why does TIMEVALUE return #VALUE! on data that looks fine?

The most common cause is a hidden space or non-breaking character left over from a CSV or database export. Run =LEN(B2) and compare it against the visible character count; if the numbers don't match, there's an invisible character breaking the parse. Wrapping the reference in TRIM, like =TIMEVALUE(TRIM(B2)), resolves this in most cases.

Why does TIMEVALUE return the wrong hour?

This usually happens with ambiguous times entered without AM or PM, like "7:00" instead of "7:00 AM". Depending on your regional settings, Excel may interpret this as either 7 AM or 19:00 in 24-hour format. Always include AM/PM in the source text when the data could be ambiguous, or confirm your system's time format before trusting the output on a new dataset.

Does TIMEVALUE work with 24-hour time formats?

Yes. TIMEVALUE accepts both 12-hour strings with AM/PM and 24-hour strings like "14:30". The result is identical either way: "2:30 PM" and "14:30" both return 0.604166666...

What happens if the text string includes seconds?

TIMEVALUE includes seconds in the calculation if they're present in the string. "9:00:30 AM" returns a value slightly higher than "9:00:00 AM", the difference representing 30 seconds as a fraction of a day. This matters for precise duration math, like measuring call center handle times down to the second.

Is TIMEVALUE the same as formatting a text cell to look like a time?

No, and this is where a lot of confusion comes from. Applying a time number format to a cell that contains text does nothing, the cell still displays as left-aligned text and can't be used in math. TIMEVALUE actually converts the underlying value into a number Excel can calculate with; formatting only changes how a number already is displayed.

Can TIMEVALUE convert a full datetime string that includes both a date and a time?

Yes, but it only returns the time portion and silently drops the date. Feed it "3/15/2026 9:00 AM" and you'll get 0.375 back, the same result as passing "9:00 AM" alone. If you need the date preserved, extract it separately with DATEVALUE and add the two results together.

Related Functions

Function	Use this when...
DATEVALUE	You need to convert a text date string into a real date serial number, often alongside TIMEVALUE to build a full datetime.
TEXT	You need to go the other direction: convert a real time value back into a formatted text string for display or concatenation.
TIME	You already have separate hour, minute, and second numbers and want to build a time value directly, instead of parsing a text string.
HOUR	You need to pull just the hour component out of a time value, often chained after TIMEVALUE on imported text.
NOW	You need the current date and time as a live value, rather than converting a static text string.