TIME on Excel (Table of Contents)
- TIME in Excel
- Formula of Time in Excel
- TIME Function – Usage Notes
- How to Open TIME Function?
- How to Use TIME in Excel Sheet?
- Excel TIME Function Errors
TIME in Excel
The Microsoft Excel TIME Function is a function of excel, which is built in and allows you for creating a time with individual hour, minute and second components. TIME Function in Excel is helpful in assembling the proper time inside any other formula. It is categorized under the DATE & TIME Functions. The TIME in Excel will return a decimal value between 0 and 0.999988426 giving an hour, minute and the second value. Let’s make it more clear, the value 0 will represent 12:00:00 AM and the value 0.999988426 will represent 11:59:59 PM. The TIME function in Excel is a worksheet function and can be used as a part of the formula in a cell of a Microsoft Excel worksheet.
TIME Formula in Excel
Explanation of TIME Formula in Excel
The formula of TIME accepts the following parameters and arguments:
- hour – This can be any number between 0 and 32767, which represents the hour. The point to be noted for this argument is that if the hour value is larger than 23, it will be then 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, which represents the minute. For this argument, you should note that if the minute value is larger than 59, then every 60 minutes will add up 1 hour in the preexisting 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, which represents the second. For this argument, you should note that, if the second value exceeds 59, then every 60 seconds will be adding 1 minute to the preexisting 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 in Excel:
The return value will be a numeric value between 0 and 0.999988426, which represents a particular time in excel sheet.
TIME Function in Excel – Usage Notes
- The TIME in excel sheet is used for creating a date in serial number format from the hour, minute and seconds components, which you specify.
- TIME sheet is used to create a valid time, if you have the above components values 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 a valid time, you can easily format it as per your requirements.
How to Open the TIME Function in Excel?
- You can simply enter the desired TIME formula in the required cell to attain a return value on the argument.
- You can manually open the TIME formula in Excel dialogue box in the spreadsheet and enter the logical values to attain a return value.
- Consider the screenshot below to see the Formula of TIME option under the Date & Time Function in Excel menu.
- Click on the TIME option. The TIME formula in Excel dialogue box will open where you can put the argument values to obtain a return value.
How to Use TIME in Excel Sheet?
Take a look at the below-given examples of TIME in Excel Sheet. These Time Function examples will help you in exploring the use of TIME function in Excel and make the concept of TIME function more clear.
In the above screenshot, you can see that 3 columns have been included, i.e. D, E and F. These columns have different formats which show the results generated from the TIME function. Consider the following points for better understanding.
- Column D cells are formatted with the General format so that the result from the TIME function in decimal values is displayed.
- Column E cells are formatted with h:mm AM/PM default format. This is how excel automatically format the results once you enter the TIME formula in Excel.
- Column F cells are formatted with h:mm:ss AM/PM custom format. This will help you in seeing the full hours, minutes and seconds.
Now, based on the above Excel spreadsheet, let’s consider eight examples and see the TIME function return based on the syntax of the Time in Excel function.
Note- You have to enter the Formula of TIME in all cells of three columns D, E and F to attain the result.
Consider the below screenshots of the above examples for clear understanding.
TIME Example #1
TIME Example #2
TIME in Excel Sheet Example #3
TIME Example #4
TIME in Excel Sheet Example #5
TIME in Excel Sheet Example #6
TIME in Excel Sheet Example #7
TIME in Excel Sheet Example #8
Excel TIME Function Errors
If you get any type of error from the TIME function in excel, it can be any 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 supplied by you in non-numeric.
Things to Remember About the TIME Function in Excel
- TIME on Excel allows you to create a time with individual hour, minute and second components.
- TIME on Excel is categorized under the DATE & TIME Functions.
- The TIME on Excel 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 a valid time, you can easily format it as per your requirements.
You can download the sample Excel file containing the examples explained above. – TIME Function Excel Template
This has been a guide to TIME Function in Excel. Here we discuss the TIME Formula in excel and how to use TIME function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –