Author: Oscar Cronquist Article last updated on September 16, 2022

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:

=TIME(B3, C3, D3)

1. TIME Function Syntax

TIME(hour, minute, second)

Back to top

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.

Back to top

3. What is time in Excel?

excel clock1

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

Back to top

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:

=TIME(B3, C3, D3)

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

Back to top

4.1 Time function - Hour value larger than 24

TIME function more than 24 hours

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

TIME function more than 60 minutes

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

TIME function more than 60 seconds

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.

Back to top

5. TIME Function - how to add hours

TIME function add hours

Formula in cell F3:

=B3+D3/24

Alternative formula:

=TIME(HOUR(B3) + D3, MINUTE(B3), SECOND(B3))

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

Back to top

6. TIME Function - how to add minutes

TIME function add minutes

Formula in cell F3:

=B3+D3/1440

Alternative formula:

=TIME(HOUR(B3), MINUTE(B3) + D3, SECOND(B3))

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

Back to top

7. TIME Function - how to add seconds

TIME function add seconds

Formula in cell F3:

=B3+D3/86400

Alternative formula:

=TIME(HOUR(B3), MINUTE(B3), SECOND(B3) + D3)

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

Back to top

8. How to calculate more than 24 hours?

TIME function show 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.

  1. Select cell F3.
  2. Press CTRL + 1 to open the "Format Cells" dialog box.
  3. Select the "Custom" category.
  4. Use the following formatting code:
    [h]:mm:ss
  5. 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

Back to top

9. Convert time to 24 hour clock

TIME function 24 hour clock

The TEXT functionlets you convert AM/PM to a 24 hour clock.

Formula in cell D3:

=TEXT(B3,"hh:mm:ss")

Explaining formula

Step 1 - TEXT function

The TEXT function converts a value to text in a specific number format.

TEXT(valueformat_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(valueformat_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.

Back to top