TIME in Google Sheets: Build Clock Values, Normalize Overflow and Truncate Components
Build Google Sheets times from numeric components, understand silent overflow and decimal truncation, and keep time-of-day values distinct from elapsed durations.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: TIME(hour,minute,second) constructs a time from three numeric components. =TIME(8,15,30) represents 08:15:30. Google documents automatic overflow normalization and decimal truncation, so a returned time is not proof that the original components were valid.
Availability and version history
- Introduced: first introduction date is not established by the official history reviewed; no desktop release number is inferred.
- Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
- Checked: October 14, 2026; official TIME reference. Documentation and independent models, with no native Google Sheets execution or wider client certification.
Syntax and required types
=TIME(hour, minute, second)
All three arguments are required numbers. A string or a reference containing a string produces documented #VALUE!. For example, the numeric 8 and quoted text "8" are different inputs under this rule.
Format the output as a time to inspect its clock value. A number format instead shows its fraction of a 24-hour day. Keep source components separate from outputs when validating an imported time.
Example 1: build an ordinary time
=TIME(8,15,30)
=TIME(12,0,0)
=TIME(0,0,0)
Expected clock values are 08:15:30, 12:00:00 and 00:00:00. Their numeric fractions are 29730/86400, 0.5 and 0, respectively. The first is approximately 0.3440972222.
The values represent a time of day. They do not independently name a calendar date, time zone or event. A business record requiring all of those fields needs more context than a TIME result.
Example 2: trace overflow instead of treating it as an error
Google explicitly illustrates hour 25 becoming 01:00 and a second value of 60 advancing the minute. These related independently calculated controls follow that normalization rule:
| Formula | Expected clock time |
|---|---|
| =TIME(25,0,0) | 01:00:00 |
| =TIME(12,0,60) | 12:01:00 |
| =TIME(0,90,0) | 01:30:00 |
| =TIME(0,0,125) | 00:02:05 |
| =TIME(1,75,90) | 02:16:30 |
For the last control, 75 minutes contribute one hour and fifteen minutes, while 90 seconds contribute one minute and thirty seconds. The total is 8190 seconds, or 8190/86400 of a day.
If 75 was an invalid minute entry, normalization conceals rather than rejects that mistake. Retain the original component row when input quality matters.
Advanced example: decimal components are not decimal hours
=TIME(12.75,0,0)
=TIME(12,45,0)
=TIME(8.9,15.9,30.9)
Under Google’s explicit truncation rule, expected times are 12:00:00, 12:45:00 and 08:15:30. The first input does not mean twelve hours and forty-five minutes; the .75 part is discarded.
If the source quantity really is 12.75 hours, divide that duration by 24 to obtain 0.53125 days. The intended clock equivalent is 12:45, but that interpretation must come from the source units, not an accidental TIME truncation.
Advanced example: validate a numeric component row
For components already established as numeric, put hour, minute and second in A2:C2 and test:
=AND(A2>=0,A2<24,A2=INT(A2),B2>=0,B2<60,B2=INT(B2),C2>=0,C2<60,C2=INT(C2))
The row 8, 15, 30 gives an expected TRUE; 25, 0, 0, 8, 75, 0 and 8.5, 15, 30 each give FALSE. The rule deliberately permits only conventional whole-number components.
This formula is demonstrated after numeric types are established. It is not a promise that AND safely skips invalid arguments or that every error-bearing imported field can be checked by this one expression.
Advanced example: combine a date and a clock value
=DATE(2026,10,14)+TIME(8,15,30)
Expected display: 2026-10-14 08:15:30. The numeric result is 46309+29730/86400 using the documented Sheets date base.
Compare =DATE(2026,10,14)+TIME(27,0,0) with =DATE(2026,10,14)+27/24. The expected displays are October 14 at 03:00 and October 15 at 03:00. TIME wraps the clock; a duration of 27 hours retains the additional day.
Elapsed durations and unverified boundaries
A 24-hour duration is one numeric day, while the normalized TIME(24,0,0) clock is midnight. A 1500-minute duration is 25 hours; its normalized TIME clock is 01:00. Do not use TIME to store a long elapsed total when losing full days would change the result.
Google does not state a complete negative-input, maximum-component or overflow-error table in this reference. No Excel numeric limit or error code is imported here. Fractional-second precision is also different from component truncation: use the documented TIMEVALUE fractional-second text example when that parsing task is intended, and verify its downstream use separately.
References and verification
Examples use fictional inputs, English function names and comma argument separators. Expected outputs are independently modeled from the cited rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.