TIMEVALUE function

Converts time text into a decimal number enabling calculations with warehouse receipt times, shipping schedules, and processing timestamps.

=TIMEVALUE(time_text)

Generate a TIMEVALUE formula

Describe what you need. The generator will reach for TIMEVALUE where TIMEVALUE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How TIMEVALUE reads its arguments
time_textrequiredTIMEVALUE →
ArgumentRequiredDescription
time_textRequiredA text string in a recognized time format (e.g., "14:30:00", "2:30 PM"). If time_text is not a valid time, TIMEVALUE returns #VALUE! error.

Returns

A decimal number between 0 and 1 representing the fraction of a 24-hour day.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Convert a warehouse receipt time from text to decimal

SKUWarehouseOn HandReorder PointCostLast Receipt Time
A001NYC15010025.5014:30:00
B002LA457518.7509:15:30
C003CHI20012532.0016:45:00
=TIMEVALUE("14:30:00")

Result: 0.604166667

The time string "14:30:00" represents 14.5 hours into a 24-hour day. TIMEVALUE converts this to 0.604166667 (14.5 ÷ 24). This decimal format enables time arithmetic in formulas and enables comparisons across different cells for scheduling.

2. Calculate hours elapsed between two warehouse receipt times

SKUWarehouseOn HandReorder PointCostLast Receipt Time
A001NYC15010025.5014:30:00
B002LA457518.7509:15:30
C003CHI20012532.0016:45:00
=(TIMEVALUE("16:45:00") - TIMEVALUE("09:15:30")) * 24

Result: 7.5

To find the time difference between two receipt times, subtract the decimal values and multiply by 24 to convert back to hours. Here, 16:45:00 minus 09:15:30 equals 7.5 hours. This is useful for calculating warehouse shift durations and measuring time between successive deliveries.

3. Check if a receipt arrived during warehouse operating hours

SKUWarehouseOn HandReorder PointCostLast Receipt Time
A001NYC15010025.5014:30:00
B002LA457518.7509:15:30
C003CHI20012532.0016:45:00
=IF(AND(TIMEVALUE(D2)>=TIMEVALUE("08:00:00"), TIMEVALUE(D2)<=TIMEVALUE("18:00:00")), "In Hours", "After Hours")

Result: In Hours

By converting both the stored receipt time (14:30:00) and warehouse boundaries (08:00:00 and 18:00:00) to decimals, you can compare them directly. Since 0.604 falls between 0.333 and 0.750, the receipt arrived during business hours and returns "In Hours".

Common errors

Which TIMEVALUE error are you seeing?
TIMEVALUE returned an error#VALUE!
Verify the time string uses valid ranges: hours 0–23, minutes 0–59, seconds 0–59. Use a TIMEVALUE() wrapping IFERROR() to detect invalid entries, or inspect the source data for formatting errors.
#VALUE!
Check the source cell is not empty. Use =IFERROR(TIMEVALUE(D2), 0) to substitute a default value (like 0 for midnight), or filter and validate source data before conversion.
#VALUE!
Ensure the time string matches your spreadsheet's locale and language settings. Standardize separators (use colons for HH:MM:SS), and test with a known valid time first to confirm compatibility.
ErrorWhy it happensHow to fix it
#VALUE!time_text contains an invalid time format, such as "25:00:00" (hour exceeds 23), "13:60:00" (minute exceeds 59), or non-time text like "2026-09-16" or "ABC".Verify the time string uses valid ranges: hours 0–23, minutes 0–59, seconds 0–59. Use a TIMEVALUE() wrapping IFERROR() to detect invalid entries, or inspect the source data for formatting errors.
#VALUE!time_text is empty, null, or references a completely blank cell. TIMEVALUE cannot convert empty input or missing data to a time value.Check the source cell is not empty. Use =IFERROR(TIMEVALUE(D2), 0) to substitute a default value (like 0 for midnight), or filter and validate source data before conversion.
#VALUE!time_text uses an unrecognized locale-specific format or separator. For example, some regions use periods (.) instead of colons (:) for time components, or TIMEVALUE does not recognize the AM/PM designation or regional time conventions.Ensure the time string matches your spreadsheet's locale and language settings. Standardize separators (use colons for HH:MM:SS), and test with a known valid time first to confirm compatibility.

Tips and when to use something else

  • TIMEVALUE only extracts the time portion; if you pass a full datetime like "2026-09-16 14:30:00", most spreadsheets ignore the date and return just the time decimal. Test with your platform to confirm behavior.
  • Times are stored as decimals from 0 (midnight) to 0.999... (23:59:59). To extract hours from a TIMEVALUE result, multiply by 24 and use INT(); for minutes, multiply by 1440 and use INT().
  • For extracting time components, prefer HOUR(), MINUTE(), or SECOND() functions over TIMEVALUE calculations—they're clearer and avoid floating-point rounding errors in complex formulas.
  • TIMEVALUE is for text-to-decimal conversion only. If your source is already a time value in a cell, use TIME() to construct times, or NOW() for the current time. Do not chain TIMEVALUE calls unnecessarily.

Related functions

TIMETIME converts hours, minutes, and seconds into a decimal time value between 0 and 1, representing a time on a 24-hour clock.HOURHOUR extracts the hour component from a time value, returning a number between 0 and 23 representing the hour of the day.MINUTEMINUTE extracts the minute component (0-59) from a time value, returning an integer representing the minutes elapsed in the current hour.SECONDSECOND extracts the seconds component from a time value or timestamp, returning an integer from 0 to 59 for precise time-based analysis.NOWReturns the current date and time as a timestamp value that updates automatically each time the spreadsheet recalculates.

Frequently asked questions

What time formats does TIMEVALUE recognize?
TIMEVALUE accepts most common formats: "HH:MM:SS", "HH:MM" (seconds optional), "H:MM AM/PM", and times matching your system locale. Exact formats vary by spreadsheet (Excel vs. Google Sheets); test with your platform to confirm which formats are supported.
How do I extract just the hours from a TIMEVALUE result?
Multiply the TIMEVALUE decimal by 24 and use INT(): =INT(TIMEVALUE("14:30:00") * 24) returns 14. For a cleaner approach, use the HOUR() function directly on a time value.
Can I compare two TIMEVALUE results directly?
Yes. Since TIMEVALUE returns decimals, use comparison operators (<, >, =) directly: =IF(TIMEVALUE("14:30:00") > TIMEVALUE("12:00:00"), "Afternoon", "Before Noon"). Decimals sort numerically, so earlier times (smaller decimals) always sort before later times.
What's the difference between TIMEVALUE and TIME()?
TIME() constructs a time from separate numeric arguments: =TIME(14, 30, 0). TIMEVALUE() parses a text string: =TIMEVALUE("14:30:00"). Use TIMEVALUE when data arrives as text from imports or user input; use TIME() when you have numeric hour, minute, and second components.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17