Numbers TIMEVALUE: Day Fractions, Decimal Hours and Overnight Limits
Use Numbers TIMEVALUE to convert clock values into day fractions, decimal hours and seconds, with checked source examples and explicit overnight assumptions.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
TIMEVALUE returns a number representing a time of day as a fraction of twenty-four hours. Noon is 0.5, and 9 pm is 0.875. These are numeric day fractions, rather than a displayed clock string or a Numbers duration value.
The date part of a date/time input does not encode elapsed days in this result. Two events at noon on different dates have the same time-of-day fraction; their full timestamps are needed to measure the interval between them.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked references.
- Availability: Currently documented in Apple’s Functions Help 15.4 documentation edition; no final supported-version endpoint is established.
- Scope: Shared iWork reference. Individual historical Mac, iPad, iPhone and iCloud endpoints and imported-workbook parity remain unverified.
- Checked: October 14, 2026, against the official TIMEVALUE reference. Calendar, time-fraction and text checks were independently calculated; no native Numbers test was performed.
Syntax and a direct numeric reconstruction
TIMEVALUE(time)
The TIMEVALUE reference accepts a time string or a date/time value. There is one argument and no documented default. For ordinary hour, minute and second inputs, the independently calculated fraction is:
(hour*3600 + minute*60 + second)/86400
A decimal fraction can be formatted as a number for inspection. A trailing display zero or a clock format does not change its numerical value. For a genuine duration, use the dedicated DURATION family rather than assuming every time-related function returns the same type.
Reproduce all three direct Apple examples
Create TimeStrings, with seven columns A:G and six rows including a header row. Label A1 Time text, B1 Day fraction, C1 Hours, D1 Minutes, E1 Seconds, F1 Numeric result and G1 Source is text.
Set A2:A4 to Text format before entering these strings without enclosing quotation marks:
| Row | A: Time text | B: expected fraction | C: expected hours |
|---|---|---|---|
| 2 | 4/6/88 12:00 | 0.5 | 12 |
| 3 | 12:00:59 | 0.50068287037037 | 12.0163888889 |
| 4 | 9:00pm | 0.875 | 21 |
These are Apple’s literal strings. Their date notation and AM/PM parsing should be checked against the target settings; the first is quoted as an official example, not a recommendation to use ambiguous two-digit-year dates for new imports.
Put =TIMEVALUE(A2) in B2, =B2*24 in C2, =B2*1440 in D2, =B2*86400 in E2, =ISNUMBER(B2) in F2 and =ISTEXT(A2) in G2. Fill B2:G2 through row 4.
Expected minute values are 720,720.9833333333,1260; second values are 43200,43259,75600. F and G have the documentation-based expectation TRUE. The decimal figures shown here are rounded, while the exact second totals preserve the underlying simple fractions.
Use numeric time components to avoid string parsing
Create ClockParts, with eight columns A:H and eight rows including a header row. Put Hour, Minute, Second, Clock value, Day fraction, Independent fraction, Difference and Decimal hours in A1:H1. Enter:
| Row | A | B | C | Expected E |
|---|---|---|---|---|
| 2 | 0 | 0 | 0 | 0 |
| 3 | 6 | 0 | 0 | 0.25 |
| 4 | 12 | 0 | 0 | 0.5 |
| 5 | 16 | 45 | 30 | 0.69826388888889 |
| 6 | 21 | 0 | 0 | 0.875 |
In D2 enter =TIME(A2,B2,C2), E2 =TIMEVALUE(D2), F2 =(A2*3600+B2*60+C2)/86400, G2 =E2-F2 and H2 =E2*24. Fill D2:H2 through row 6.
Expected differences in G are mathematically zero; small native numerical residuals have not been measured. H gives 0,6,12,16.7583333333,21 decimal hours. TIME creates a date/time value from numeric components; TIMEVALUE then extracts its day fraction.
The official TIME entry permits values exceeding a day and wraps them. These controls stay within one day, so they do not confuse a wrapped clock value with an elapsed duration of several days.
Advanced: a carefully bounded overnight interval
Create Overnight, with six columns A:F and six rows including a header row. Put Start, End, Start fraction, End fraction, Hours with one rollover and Simple difference hours in A1:F1.
- A2:
=TIME(22,30,0) - B2:
=TIME(1,15,0) - C2:
=TIMEVALUE(A2) - D2:
=TIMEVALUE(B2) - E2:
=(D2-C2+1)*24 - F2:
=(D2-C2)*24
The known next-day interval is 2.75 hours. The simple same-day subtraction is -21.25 hours. Start and end fractions are 0.9375 and 0.05208333333333 respectively.
This particular formula adds one day because the scenario explicitly says the end is on the next day. It is not a general elapsed-time calculation. For a same-day interval from 09:00 to 17:30, enter =TIME(9,0,0) in A3 and =TIME(17,30,0) in B3. Fill C2:D2 down to row 3, then put =(D3-C3)*24 in E3 for 8.5. Do not copy the extra-day formula from E2 into that row.
Equal clock times can mean zero hours, twenty-four hours or more, depending on the dates. TIMEVALUE alone cannot resolve that ambiguity. Retain full timestamps for real multi-day, timezone or daylight-saving calculations; this exercise models ordinary twenty-four-hour clock arithmetic only.
Parsing and precision limits
The short TIMEVALUE entry does not give a full invalid-string error table, sub-second precision contract or timezone conversion rule. Literal strings, typed date/time cells, durations and imported serial numbers should not be treated as interchangeable without checking their types and settings.
ISNUMBER checks the numeric result. DATEVALUE returns a date/time value under a different conversion contract. Formula separators follow the document locale; no localized or native import test is claimed here.