TIME, HOUR and MINUTE in Excel: how to use it
TIME builds a time from an hour, minutes and seconds. HOUR and MINUTE take those parts out again, such as the hour 17 from 17:45.
Try the first exercises for freeIn the lesson Calculating with times and hours
What do TIME, HOUR and MINUTE do?
TIME makes a real time from separate numbers: the hour, the minutes and the seconds. That's handy if they're in separate columns, or if you want to add a fixed length of time, such as a quarter of an hour: =A2+TIME(0,15,0).
HOUR, MINUTE and SECOND do the opposite: they take one part out of a time. =HOUR(A2) gives 17 for 17:45, and =MINUTE(A2) gives 45. The result is an ordinary number, which you can count with or compare.
When do you use TIME, HOUR and MINUTE?
- The hour and the minutes are in separate columns: =TIME(A2,B2,0).
- Adding a fixed length of time to a time, such as 45 minutes: =A2+TIME(0,45,0).
- Taking the hour out of a time, to count per hour: =HOUR(B2).
- Taking the minutes out of a time, for example to see who arrived after the hour: =MINUTE(B2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
A time from an hour, minutes and seconds.
Filled in: =TIME(A2,B2,0)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
TIME | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | TIME |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
hour | The hour, from 0 to 23. | A2 |
minute | The minutes, from 0 to 59. | B2 |
second | The seconds. If you don't have any, type 0. | 0 |
, | A comma separates the arguments from each other. | , |
TIME, HOUR and MINUTE step by step
C2 contains =TIME(A2,B2,0). From 8 and 30, TIME makes the time 8:30. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C2 and type TIME with a bracket.
=TIME( - Click the hour in A2 and type a comma.
=TIME(A2, - Click the minutes in B2, type a comma and 0 for the seconds.
=TIME(A2,B2,0 - Close the bracket and press Enter: 8:30. If you see a number such as 0.354, choose a time format.
=TIME(A2,B2,0)
More examples
The hour from a time
B2 contains =HOUR(A2). HOUR gives 17. The minutes don't count.
The minutes from a time
B2 contains =MINUTE(A2). MINUTE gives 45.
A quarter of an hour later
B2 contains =A2+TIME(0,15,0). TIME(0,15,0) is a quarter of an hour: 9:50 plus a quarter of an hour is 10:05.
Where it often goes wrong
- TIME starts again after 24 hours: =TIME(25,0,0) gives 1:00.
- HOUR and MINUTE give a number, not a time: give the cell the General format.
Practise this in the lesson Calculating with times and hours. The first exercises are free.
Frequently asked questions
How do I add 45 minutes to a time?
With =A2+TIME(0,45,0). Or type the duration in a cell, such as 0:45, and add that.