totn Excel Functions

MS Excel: How to use the TIME Function (WS)

This Excel tutorial explains how to use the Excel TIME function with syntax and examples.

Description

The Microsoft Excel TIME function returns a decimal number between 0 and 0.999988426 given an hour, minute and second value. A result of 0 represents 12:00:00 AM and a result of 0.999988426 represents 11:59:59 PM.

The TIME function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the TIME function can be entered as part of a formula in a cell of a worksheet.

Excel TIME function

TIP: If your cell is formatted as General and you enter the TIME function, Excel will format your result as h:mm AM/PM (column E) based on your Regional Settings. If you wish to see the decimal result (column D) from the TIME function, you will have to change the format of the cell to General after entering the formula.

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the TIME function in Microsoft Excel is:

TIME( hour, minute, second )

Parameters or Arguments

hour
A number from 0 to 32767, representing the hour. If the hour value is greater than 23, it will be divided by 24 and the remainder will be used as the hour value. This means that TIME(24,0,0) is equal to TIME(0,0,0) and TIME(25,0,0) is equal to TIME(1,0,0) ... and so on.
minute
A number from 0 to 32767, representing the minute. If the minute value is greater than 59, then every 60 minutes will add 1 hour to the hour value. This means that TIME(0,60,0) is equal to TIME(1,0,0) and TIME(0,120,0) is equal to TIME(2,0,0) ... and so on.
second
A number from 0 to 32767, representing the second. If the second value is greater than 59, then every 60 seconds will add 1 minute to the minute value. This means that TIME(0,0,60) is equal to TIME(0,1,0) and TIME(0,0,120) is equal to TIME(0,2,0) ... and so on.

Returns

The TIME function returns a numeric value between 0 and 0.999988426.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel TIME function examples and explore how to use the TIME function as a worksheet function in Microsoft Excel:

Excel TIME function

In this example, we have included 3 columns (columns D, E and F) with the different formats that show the results from the TIME function:

  • In column D, we have formatted the cells with General format so that the decimal result from the TIME function is displayed.
  • In column E, we use the default format of h:mm AM/PM which is how Excel will format the results after you enter the formula.
  • In column F, we have formatted the cells with a custom format of h:mm:ss AM/PM so that you can see the full hours, minutes and seconds.

Based on the Excel spreadsheet above, the TIME examples would return the following values:

=TIME(A3,B3,C3)
Result: 0            'Represents 12:00:00 AM

=TIME(A4,B4,C4)
Result: 0.448009259  'Represents 12:45:08 AM

=TIME(A5,B5,C5)
Result: 0.760763889  'Represents 6:15:00 PM

=TIME(A6,B6,C6)
Result: 0.999988426  'Represents 11:59:59 PM

=TIME(A7,B7,C7)
Result: 0.041666667  'Represents 1:00:00 AM

=TIME(A8,B8,C8)
Result: 0.125        'Represents 3:00:00 AM

=TIME(A9,B9,C9)
Result: 0.041666667  'Represents 1:00:00 AM

=TIME(15,25,9)
Result: 0.642465278  'Represents 3:25:09 PM

To see the result from the TIME function as a numeric value, change the format of your cell to General.

Microsoft Excel

Not sure how to do this? Read our tutorial that explains about how to Format the Display of Text in a Cell (ie: Numbers, Dates, etc).