Author: Oscar Cronquist Article last updated on March 14, 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

<span class='notranslate'>TIME</span> 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

<span class='notranslate'>TIME</span> 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

<span class='notranslate'>TIME</span> 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

<span class='notranslate'>TIME</span> 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

<span class='notranslate'>TIME</span> 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

<span class='notranslate'>TIME</span> 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?

<span class='notranslate'>TIME</span> 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

<span class='notranslate'>TIME</span> 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