# 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 signsUse 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 OscarYou can contact me through this contact form