How to use the TIME function

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)

TIME Function Syntax
TIME Function Arguments
What is time in Excel?
TIME Function example
TIME function - how to add hours
TIME function - how to add minutes
TIME function - how to add seconds
How to calculate more than 24 hours?
Convert time to 24 hour clock
Get Excel *.xlsx file

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 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

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

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.

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

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

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

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.

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:

=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(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

The following 6 articles contain the TIME function.

Calculate time between time zones
This article demonstrates how to build a time zone chart and calculate times different than your time zone. The worksheet […]

Count entries based on date and time
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

Find empty hours in a weekly schedule
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Highlight specific time ranges in a weekly schedule
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]

How to AVERAGE time
Table of Contents How to AVERAGE time How to enter an array formula Explaining formula How to AVERAGE time hh […]

Time sheet for work
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]

Functions in this article

TIME HOUR MINUTE SECOND TEXT

Functions in 'Date and Time' category

The TIME function function is one of many functions in the 'Date and Time' category.

DATE function

Returns a number that acts as a date in the Excel environment.

DATEDIF function

Returns the number of days, months, or years between two dates. The DATEDIF function exists in order to ensure compatibility with Louts 1-2-3.

DATEVALUE function

Returns an Excel date value (serial number) based on a date stored as text.

DAY function

Extracts the day as a number from an Excel date.

DAYS function

Calculates the number of days between two dates.

EDATE function

Returns a date determined by a start date and a number representing how many months.

EOMONTH function

Returns an Excel date for the last day of a given month using a number and a start date.

HOUR function

Returns an integer representing the hour of an Excel time value.

MINUTE function

Returns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 to 59.

MONTH function

Extracts the month as a number from an Excel date.

NETWORKDAYS function

Returns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.

NETWORKDAYS.INTL function

Calculates the number of working days between two dates, excluding weekends.

NOW function

Returns the current date and time.

SECOND function

Returns an integer representing the second based on an Excel time value

TIME function

Returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.

TIMEVALUE function

Returns a decimal number based on a text string.

TODAY function

Returns the Excel date (serial number) of the current date.

WEEKDAY function

Converts a date to a weekday number from 1 to 7.

WEEKNUM function

Calculates a given date's week number based on a return_type parameter that determines which day the week begins.

WORKDAY function

Returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

YEAR function

Converts a date to a number representing the year in the date.

YEARFRAC function

Returns the fraction of the year based on the number of whole days between a start date and an end date.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the TIME function

Table of Contents

1. TIME Function Syntax

2. TIME Function Arguments

3. What is time in Excel?

4. TIME Function example

Explaining formula

Step 1 - TIME function

Step 2 - Populate arguments

Step 3 - Evaluate formula

4.1 Time function - Hour value larger than 24

4.2 Time function - Minute value larger than 59

4.3 Time function - Seconds value larger than 59

5. TIME Function - how to add hours

Explaining formula

Step 1 - Calculate hours in decimals

Step 2 - Add time

6. TIME Function - how to add minutes

Explaining formula

Step 1 - Calculate hours in decimals

Step 2 - Add time

7. TIME Function - how to add seconds

Explaining formula

Step 1 - Calculate seconds in decimals

Step 2 - Add time

8. How to calculate more than 24 hours?

Explaining formula in cell F3

Step 1 - Calculate Excel time value

Step 2 - Verify calculation

9. Convert time to 24 hour clock

Explaining formula

Step 1 - TEXT function

Step 2 - Populate TEXT function arguments

Step 3 - Evaluate TEXT function

'TIME' function examples

Functions in this article

Functions in 'Date and Time' category

Excel function categories

Excel categories

Leave a Reply

How to comment