Excel TIME Function
last modified October 5, 2026
The TIME function is a powerful tool in Excel for working with
time values. It builds a time from separate hour, minute, and second numbers and
returns the decimal time value that Excel recognizes. This tutorial provides a
comprehensive guide to using the TIME function with detailed
examples. You'll learn the basic syntax, how the arguments roll over, how times
are stored as serial numbers, and how TIME compares with
TIMEVALUE, HOUR, MINUTE, and
SECOND.
TIME Function Basics
The TIME function creates a time value from individual hour,
minute, and second components. It returns a decimal number representing a
fraction of a day. The syntax is straightforward but powerful.
| Component | Description |
|---|---|
| Function Name | TIME |
| Syntax | =TIME(hour, minute, second) |
| Arguments | hour, minute, second (all required) |
| Return Value | A decimal from 0 up to just under 1, representing a time of day |
This table breaks down the essential components of the TIME
function. It shows the function name, basic syntax format, required arguments,
and return value characteristics.
| Argument | Meaning |
|---|---|
| hour | A number from 0 to 32767. Values greater than 23 are divided by 24 and
the remainder is used, so TIME(27,0,0) is the same as
TIME(3,0,0). |
| minute | A number from 0 to 32767. Values greater than 59 roll over into hours,
so TIME(0,75,0) is 1:15 AM. |
| second | A number from 0 to 32767. Values greater than 59 roll over into minutes
and hours, so TIME(0,0,3600) is 1:00 AM. |
All three arguments are required, and each accepts a literal, a cell reference,
or an expression that returns a number. Negative arguments, or arguments above
32767, produce a #NUM! error.
How Excel Stores Times as Serial Numbers
Excel does not store a time as text. It stores a decimal number in which the
whole part is a date and the fractional part is the time of day. One full day is
1.0, so noon is 0.5, 6:00 AM is 0.25, and 6:00 PM is 0.75. One second is
1/86400 of a day. The number that TIME returns is only the
fractional part, which is why it always falls between 0 and 1.
| Time | Serial number |
|---|---|
| 12:00 AM | 0 |
| 6:00 AM | 0.25 |
| 12:00 PM | 0.5 |
| 6:00 PM | 0.75 |
| 6:30 PM | 0.770833 |
| 11:59:59 PM | 0.999988 |
If a cell is formatted as General, the result of TIME appears as a
date, often Jan 0, 1900, because the value is a fraction with no
whole-number date part. Apply a time number format such as hh:mm:ss
or h:mm AM/PM to display the value as a clock time.
Basic TIME Example
This example demonstrates the simplest use of the TIME function with basic time components.
=TIME(9, 30, 0)
This formula returns 0.395833, the serial number for 9:30 AM. When the cell is
formatted as time, the same value displays as 9:30:00 AM. This shows how
TIME converts separate components into a recognizable time.
TIME with Cell References
A more practical use of TIME involves building a time from values
in specific cells. Here's an example with cell references.
| A | B | C | D |
|---|---|---|---|
| 14 | 15 | 30 | |
| =TIME(A1,B1,C1) |
The table shows a simple spreadsheet with time components in columns A-C and a
TIME formula in cell D1 that combines these into a time value.
=TIME(A1,B1,C1)
This formula creates a time from cells A1 (hours), B1 (minutes), and C1
(seconds). With 14, 15, and 30, the result is 0.594097, which displays as
2:15:30 PM. Using cell references makes TIME dynamic, so the result
updates whenever the inputs change.
Roll-Over of Out-of-Range Arguments
TIME automatically adjusts values that exceed their normal ranges. Minutes and seconds are carried into the next larger unit in the same way an ordinary clock works, and hours wrap around every 24 hours.
| Formula | Serial number | Displays as |
|---|---|---|
| =TIME(27,0,0) | 0.125 | 3:00 AM |
| =TIME(0,75,0) | 0.052083 | 1:15 AM |
| =TIME(0,0,3600) | 0.041667 | 1:00 AM |
| =TIME(0,750,0) | 0.520833 | 12:30 PM |
| =TIME(25,70,90) | 0.091319 | 2:11:30 AM |
=TIME(27, 0, 0) =TIME(0, 75, 0) =TIME(0, 0, 3600) =TIME(25, 70, 90)
The first formula keeps only 27 modulo 24, which is 3, and returns 3:00 AM. The
second adds 75 minutes, which is 1 hour and 15 minutes, so it returns 1:15 AM.
The third turns 3600 seconds into exactly 1 hour. The last formula combines 25
hours, 70 minutes, and 90 seconds; the total is 94290 seconds, or 1 day plus
7890 seconds, so the day is discarded and the result is 2:11:30 AM. This
adjustment makes TIME robust for time calculations.
Adding and Subtracting Times
Because a time is just a decimal fraction, you can add or subtract a
TIME value to shift an existing time by a fixed interval.
| A | B |
|---|---|
| 8:00 AM | |
| =A1+TIME(2,30,0) |
The table illustrates how to add a time interval (2 hours 30 minutes) to an
existing time value (8:00 AM) using the TIME function.
=A1+TIME(2,30,0) =A1-TIME(2,30,0)
The first formula adds 2 hours and 30 minutes to 8:00 AM and returns 10:30 AM.
The second subtracts the same interval and returns 5:30 AM. If a subtraction
would move the time before 12:00 AM, the result becomes negative and a
time-formatted cell displays #####. Wrap the expression in
MOD, as in =MOD(A1-TIME(2,30,0),1), to keep the
result within a single day.
TIME with Fractions of Seconds
TIME accepts fractional seconds for precise time calculations. This example shows TIME with decimal seconds.
=TIME(12, 30, 45.75)
This formula returns 0.521363, the serial number for 12:30:45.75 PM, which
includes three-quarters of a second. Excel keeps the fraction in the serial
number, but a standard hh:mm:ss format rounds the display to the
nearest second, so this value appears as 12:30:46. Use a custom format such as
hh:mm:ss.00 if you need to see the hundredths. This precision is
valuable for scientific or technical applications requiring exact timing.
TIME vs TIMEVALUE and HOUR/MINUTE/SECOND
TIME, TIMEVALUE, HOUR,
MINUTE, and SECOND all deal with the same serial
numbers but move in different directions. TIME builds a serial from
numeric components, while TIMEVALUE parses text that looks like a
time and returns a serial. The three extraction functions do the opposite of
TIME: they read a serial and return one component as a plain number.
| Function | Direction | Example | Result |
|---|---|---|---|
| TIME | Components to serial | =TIME(14,30,0) | 0.604167 |
| TIMEVALUE | Text to serial | =TIMEVALUE("2:30 PM") | 0.604167 |
| HOUR | Serial to hour | =HOUR(0.604167) | 14 |
| MINUTE | Serial to minute | =MINUTE(0.604167) | 30 |
| SECOND | Serial to second | =SECOND(0.604167) | 0 |
=TIME(HOUR(A1), MINUTE(A1), SECOND(A1))
This formula rebuilds the original time from its components. For a cell
containing 16:25:38 it returns the same value as A1, because TIME
is the exact inverse of the three extraction functions. Use
TIMEVALUE when the time arrives as text, and TIME when
you already have separate numbers or want to assemble a time from calculations.
TIME with Other Time Functions
TIME works well with HOUR, MINUTE, and
SECOND. This example extracts components directly from a
TIME result.
| A | B | C | D |
|---|---|---|---|
| =TIME(8,45,30) | =HOUR(B1) | =MINUTE(B1) |
The table shows how to create a time value and then extract its components. Column B creates the time, while C and D extract the hour and minute portions.
=HOUR(TIME(8,45,30)) =MINUTE(TIME(8,45,30)) =SECOND(TIME(8,45,30))
These formulas return 8, 45, and 30 from the TIME result. This
technique is useful when you need to analyze or manipulate specific parts of a
time value, and it is the basis for formatting durations or measuring time
differences.
TIME in Conditional Formatting
TIME can be used in conditional formatting rules to highlight cells based on time criteria. This example shows a conditional formatting formula using TIME.
=A1>TIME(17,0,0)
This formula compares each cell with 5:00 PM and returns TRUE for later times.
When used as a conditional formatting rule, it helps visually identify late
times in a schedule or timesheet. The same comparison can drive an
IF formula, for example
=IF(A1>TIME(17,0,0),"Late","On time").
TIME in Array Formulas
TIME can be used in array formulas to create multiple time values simultaneously. This example shows TIME in an array context.
=TIME({9;12;15}, {0;0;0}, {0;0;0})
This array formula creates three time values: 9:00 AM, 12:00 PM, and 3:00 PM.
The result spills into multiple cells in Microsoft 365 and Excel 2021. In older
versions you must confirm the formula with Ctrl+Shift+Enter. This is
an efficient way to build a time series.
TIME Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| A negative hour, minute, or second | #NUM! error | Use only zero and positive values; times before midnight are not supported |
| An argument larger than 32767 | #NUM! error | Keep each argument within 0 to 32767 |
| Text that cannot be read as a number | #VALUE! error | Pass numbers, or parse a text time with TIMEVALUE first |
| Result shows a date such as Jan 0, 1900 | General format shows a date, not a time | Apply a time format such as hh:mm:ss or h:mm AM/PM |
| An added interval crosses midnight | The displayed hour wraps around | Format the cell as [h]:mm:ss to show elapsed hours beyond 24 |
| Subtraction produces a negative time | ##### in a time-formatted cell | Use MOD, or work with full datetime values |
| An empty cell is used as an argument | The component is treated as 0 | An empty cell counts as zero hours, minutes, or seconds |
The TIME function is essential for all Excel users working with
time data. From basic time creation to complex time-based calculations,
TIME handles it all efficiently, rolling over out-of-range
components and returning a clean serial number. Remember that the result is a
fraction of a day, so it needs a time format to display correctly, and that
negative or oversized arguments return #NUM!. Pair
TIME with TIMEVALUE for text input and with
HOUR, MINUTE, and SECOND for breaking
times apart, and you can measure, adjust, and total time without surprises.
Author
List all Excel Formulas.