| Function category | Date and time functions |
| Volatility | Non-volatile |
| Similar functions | DATE |
What does the TIME function do?
The TIME function is used to create a time value in Excel format based on specified hours, minutes, and seconds. This is useful when you want to assemble a time value from separate numbers or calculations.
Syntax:
=TIME(hour, minute, second)Arguments:
- hour — number from 0 to 32767. Values above 23 are divided by 24 and only the remainder is kept: =TIME(25,0,0) returns 01:00. Whole days are dropped — TIME always returns less than one day.
- minute — number from 0 to 32767. Values above 59 carry over into hours: =TIME(0,90,0) returns 01:30.
- second — number from 0 to 32767. Values above 59 carry over into minutes: =TIME(0,0,150) returns 00:02:30.
TIME formula examples
=TIME(14, 30, 0)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | TIME | As a number |
| 2 | 14 | 30 | 0 | 14:30=TIME(A2,B2,C2) | 0.6041667=D2*1 |
| 3 | 9 | 5 | 30 | 09:05:30=TIME(A3,B3,C3) | 0.3788194=D3*1 |
| 4 | 0 | 90 | 0 | 01:30=TIME(A4,B4,C4) | 0.0625=D4*1 |
| 5 | 0 | 0 | 150 | 00:02:30=TIME(A5,B5,C5) | 0.001736111=D5*1 |
| 6 | 23 | 59 | 59 | 23:59:59=TIME(A6,B6,C6) | 0.9999884=D6*1 |
Excel stores time as a fraction of a day: 12:00 is 0.5, 14:30 is 0.6041667. Minutes above 59 and seconds above 59 carry over: 90 minutes give 01:30.
Result: 2:30 PM (14:30). Inside the cell it is the number 0.6041667 — the fraction of a day.
If you enter:
=TIME(27, 0, 0)| A | B | C | |
|---|---|---|---|
| 1 | Formula | Shows | Value |
| 2 | =TIME(27,0,0) | 03:00=TIME(27,0,0) | 0.125 |
| 3 | =TIME(48,0,0) | 00:00=TIME(48,0,0) | 0 |
| 4 | =27/24 | 03:00 (or 27:00 as [h]:mm)=27/24 | 1.125 |
| 5 | =1+TIME(3,0,0) | 27:00 as [h]:mm=1+TIME(3,0,0) | 1.125 |
TIME keeps only the remainder after full days, so 27 hours become 03:00 and 48 hours become 00:00. For durations longer than a day, divide hours by 24 or add the days to TIME, and use the [h]:mm format.
Result: 03:00. The extra 24 hours are dropped: TIME returns 0.125, not 1.125. To keep the days, add them separately: =27/24 or =1+TIME(3,0,0), formatted as [h]:mm.
Practical applications:
- Creating time values from numbers obtained through calculations
- Forming timestamps from data
- Simplifying schedule and timing construction
- Building time values when components are in separate cells
- Calculating time intervals and durations
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | Duration, min | End | Total seconds | As time |
| 2 | 09:00 | 45 | 09:45=A2+TIME(0,B2,0) | 3725 | 01:02:05=TIME(0,0,D2) |
| 3 | 13:30 | 90 | 15:00=A3+TIME(0,B3,0) | 95 | 00:01:35=TIME(0,0,D3) |
| 4 | 22:15 | 120 | 00:15=A4+TIME(0,B4,0) | 59 | 00:00:59=TIME(0,0,D4) |
Adding TIME(0,B2,0) to a start time shifts it by B2 minutes; 22:15 + 120 minutes wraps to 00:15. TIME(0,0,D2) turns a count of seconds into hh:mm:ss.
Other Date and Time functions in Excel
TIMEVALUE, YEAR, DATE, DATEVALUE, EDATE, DAY, WEEKDAY, YEARFRAC, EOMONTH, MONTH, MINUTE, WEEKNUM and ISOWEEKNUM, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.