TIME Excel Function

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

 

TIME Formula in Excel

TIME is a time worksheet function in Excel that is used to make time from the arguments provided by the user. The arguments are in the following format: hours, minutes, and seconds. The range for the input for hours can be from 0-23, and for minutes it is 0-59 and similar for seconds. The method to use this function is as follows: =Time( Hours, Minutes, Seconds).

For example, TIME(0,0,3500) = 0.04050925 or 12:58:20 AM.

Table of contents

TIME FORMULA

Explanation

The formula of TIME accepts the following parameters and arguments:

  • hour – This can be any number between 0 and 32767, representing the hour. The point to be noted for this argument is that if the hour value is larger than 23, it will then be divided by 24. The remainder of the division will be used as the hour value. For better understanding, TIME(24,0,0) will be equal to TIME(0,0,0), TIME (25,0,0) means TIME(1,0,0) and TIME(26,0,0) will be equal to TIME(2,0,0) and so on.
  • minute - This can be any number between 0 and 32767, representing the minute. For this argument, you should note that if the minute value is larger than 59, every 60 minutes will add up to 1 hour in the pre-existing hour value. For better understanding, TIME(0,60,0) will be equal to TIME(1,0,0) and TIME(0,120,0) will be equal to TIME(2,0,0) and so on.
  • second – This can be any number between 0 and 32767, representing the second. For this argument, you should note that if the second value exceeds 59, then every 60 seconds will be added 1 minute to the pre-existing minute value. For better understanding, TIME(0,0,60) will be equal to TIME(0,1,0) and TIME(0,0,120) will be equal to TIME(0,2,0) and so on.

Return Value of TIME Formula:

The return value will be a numeric value between 0 and 0.999988426, which represents a particular timesheet.

Usage Notes

  • The TIME in the Excel sheet is used for creating a date in serial number format from the hour, minute, and seconds components, which you specify.
  • The TIME sheet creates a correct time if you have the values of the above components separately. For example, TIME(3,0,0,) is equal to 3 hours, TIME(0,3,0) is equal to 3 minutes, TIME (0,0,3) is equal to 3 seconds, and TIME(8,30,0) is equal to 8.5 hours.
  • Once you get the right time, you can easily format it as per your requirements.

How to Open the TIME Function in Excel?

 

  1. You can enter the desired TIME formula in the required cell to attain a return value on the argument.

     

  2. You can manually open the TIME formula dialogue box in the spreadsheet and enter the logical values to attain a return value.

     

  3. Consider the screenshot below to see the formula of the TIME option under the DATE and TIME function in the Excel menu.


    TIME Function Step 2

How to Use TIME in Excel Sheet?

Take a look at the below-given examples of TIME in the Excel sheet. These TIME function examples will help you explore the use of the TIME function in Excel and clarify the concept of the TIME function.

TIME Function Example 1

The above screenshot shows that three columns have been included: D, E, and F. These columns have different formats that offer the results generated from the TIME function. Consider the following points for better understanding.

  • The column D cells are formatted with the "General" format to display the result from the TIME function in decimal values.
  • The column E cells are formatted with the default h:mm AM/PM format. It is how Excel automatically formats the results once you enter the TIME formula.
  • The column F cells are formatted with h:mm:ss AM/PM custom format. It will help you see the full hours, minutes, and seconds.

Based on the above Excel spreadsheet, let us consider eight examples and see the TIME function return based on the syntax.

This topic not only highlights key concepts but also provides a broader understanding of the field. If you’re curious to explore these ideas more thoroughly, this Working With Advanced Excel Functions Course offer a structured way to dive deeper.

Note- To attain the result, you must enter the TIME formula in all three columns, D, E, and F cells.

TIME FUnction Example 1

Consider the screenshots of the above examples for a clear understanding.

Example #1

TIME Function Example1

Example #2

TIME Function Example2

Example #3

TIME Function Example3

Example #4

Example 4

Example #5

Example 5

Example #6

Example 6

Example #7

Example 7

Example #8

Example8

Excel TIME Function Errors

If you get any error from the TIME function, it can be one of the following.

#NUM! – This kind of error occurs if the argument provided by you is evaluating a negative time; for example, if the supplied hour is less than 0.

#VALUE! – This kind of error occurs if any of the arguments are supplied by you in non-numeric.

Things to Remember

  • The TIME function in Excel allows you to create time with individual hour, minute, and second components.
  • The TIME in Excel is categorized under the DATE and TIME functions.
  • The TIME function will return a decimal value between 0 and 0.999988426, giving an hour, minute, and second value.
  • After using the TIME on Excel, once you get the right time, we can easily format it as per the requirements.

TIME Function in Excel Video

Recommended Articles

This article has been a guide to TIME Function in Excel. Here, we discuss the TIME formula in Excel and how to use the TIME function, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: -

  • Subtract Time in Excel Formula
  • Timeline Chart
  • DAY Function
  • TODAY Excel Function
  • Search For Text in Excel