How to use the TIME function
What is the TIME function?
The TIME function returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.
Formula in cell D3:
Table of Contents
1. TIME Function Syntax
TIME(hour, minute, second)
2. TIME Function Arguments
| hour | Required. A number between 0 and 32767 represents the hour. |
| minute | Required. A number between 0 and 32767 represents the minute. |
| second | Required. A number between 0 and 32767 represents the second. |
3. What is time in Excel?
Excel time value is a number equal to or larger than 0 (zero) and smaller than 1, formatted as a time value. One hour is 1/24, there are 24 hours in one day.
One minute is 1/1440, there are 1440 minutes in one day (60*24 = 1440). One second is 1/86400, there are 86400 seconds in one day (60*60*24 = 86400).
The following table shows whole hours, one hour is 1/24, 2 hours is 2/24, and so on.
0 - 12:00:00 AM
1/24 - 1:00:00 AM
2/24 - 2:00:00 AM
...
23/24 - 11:00:00 PM
24/24 - 12:00:00 AM
The time value is only the decimal part of a number, in other words, a value larger than or equal to 1 makes no difference, Excel uses only the decimal part of a number to create an Excel time value.
1.5 -> 0.5 -> 12:00:00 PM
The whole numbers represent dates in Excel. The whole number and the decimal part create a date and time value. Here is an example: 1.5 represents 1/1/1900 12:00 PM
4. TIME Function example
The TIME function creates an Excel time value meaning a number equal to or larger than 0 (zero) and smaller than 1 formatted as a time value.
Formula in cell E3:
Explaining formula
Step 1 - TIME function
TIME(hour, minute, second)
Step 2 - Populate arguments
TIME(hour, minute, second)
hour - B3
minute - C3
second - D3
Step 3 - Evaluate formula
TIME(1, 30, 20)
and returns 0.062731481 (1:30:20 AM).
1 hour = 1/24
30 min = 30/1440
20 sec = 20/86400
1/24 + 30/1440 + 20/86400 = 0.062731481
4.1 Time function - Hour value larger than 24
An hour value greater than 23 will be divided by 24 and the remaining hours will be returned by the function.
27/24 = 1.125 The decimal part is 0.125 and is equal to 3 hours. 3/24 = 0.125
4.2 Time function - Minute value larger than 59
A minute value equal to or greater than 60 will be divided by 60, the whole number is hours and the remaining minutes will be minutes.
119/60 is approx. 1.983333 The TIME function returns 1:59:00 AM. 0.983333 is approx. 59 minutes.
4.3 Time function - Seconds value larger than 59
A "second" value equal to or greater than 60 will be divided by 60 and added to minutes, the remaining seconds are returned.
7200/86400 is approx. 0.083333 which is the same as 120 minutes or 2 hours.
5. TIME Function - how to add hours
Formula in cell F3:
Alternative formula:
Note, the TIME function arguments are limited to 32767. Larger values return #NUM errors.
Explaining formula
Step 1 - Calculate hours in decimals
D3/24
becomes
5/24 equals 0.208333333.
Step 2 - Add time
B3+D3/24
becomes
0.979166667 + 5/24
becomes
0.979166667 + 0.208333333 equals 1.1875
6. TIME Function - how to add minutes
Formula in cell F3:
Alternative formula:
Note, the TIME function arguments are limited to 32767. Larger values return #NUM errors.
Explaining formula
Step 1 - Calculate hours in decimals
The division slash charcater lets you divide numbers in an Excel formula.
D3/24
becomes
5/1440 is approx. 0.0034722
Step 2 - Add time
The plus sign lets you add numbers in an Excel formula.
B3+D3/1440
becomes
0.979166667 + 5/1440
becomes
0.979166667 + 0.0034722
and is approx. 0.982638889
7. TIME Function - how to add seconds
Formula in cell F3:
Alternative formula:
Note, the TIME function arguments are limited to 32767. Larger values return #NUM errors.
Explaining formula
Step 1 - Calculate seconds in decimals
D3/24
becomes
5/86400
and is approx. 0.0000578
Step 2 - Add time
B3+D3/24
becomes
0.979166667 + 5/86400
becomes
0.979166667 + 0.0000578
and is approx. 0.979224537037037
8. How to calculate more than 24 hours?
The image above shows how to display an Excel time value larger than 1. This is possible using a different cell formatting code than the default one Excel uses.
- Select cell F3.
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Select the "Custom" category.
- Use the following formatting code:
[h]:mm:ss - Press with left mouse button on OK button.
Explaining formula in cell F3
Step 1 - Calculate Excel time value
Cell F3 contains an Excel time value larger than 1.
B3+B5/24
becomes
0.979166666666667 + B5/24
becomes
0.979166666666667 + 60/24
becomes
0.979166666666667 + 2.5
and returns 3.47916666666667 in cell F3. Cell F3 is formatted using the following cell formatting code: [h]:mm:ss which shows 83:30:00 in cell F3.
Step 2 - Verify calculation
We can easily verify the calculation.
3*24 = 72 hours
0.47916666666667 * 24 equals 11.5 hours.
72 + 11.5 = 83.5 hours -> 83:30:00
9. Convert time to 24 hour clock
The TEXT functionlets you convert AM/PM to a 24 hour clock.
Formula in cell D3:
Explaining formula
Step 1 - TEXT function
The TEXT function converts a value to text in a specific number format.
TEXT(value, format_text)
| value | The string you want to format. You can use a cell reference here or use a text string. |
| format_text | Formatting code allows you to change the way, for example, a date or a number is displayed to the Excel user. |
Step 2 - Populate TEXT function arguments
TEXT(value, format_text)
value - B3
format_text - "hh:mm:ss"
hh - hours using two digits
mm - minutes (two digits)
ss - seconds (two digits)
Step 3 - Evaluate TEXT function
TEXT(B3,"hh:mm:ss")
becomes
TEXT(0.0627314814814815, "hh:mm:ss")
and returns 01:30:20.
1/24 + 30/1440 + 20/86400 equals 0.0627314814814815.
'TIME' function examples
This article demonstrates how to build a time zone chart and calculate times different than your time zone. The worksheet […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
Functions in 'Date and Time' category
The TIME function function is one of 22 functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form