TIME Formula in Excel
Time is a time worksheet function in excel which is used to make time from the arguments provided by the user, the arguments are in the following format respectively 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 and the method to use this function is as follows =Time( Hours, Minutes, Seconds).
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:
The return value will be a numeric value between 0 and 0.999988426, which represents a particular timesheet.
- 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 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 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 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.
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 that 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 formats the results once you enter the TIME formula.
- 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 function.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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.
Excel TIME Function Errors
If you get any type of error from the TIME function, 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
- TIME on Excel allows you to create time with individual hour, minute and second components.
- TIME on Excel is categorized under the DATE & TIME Functions.
- The TIME will return a decimal value between 0 and 0.999988426 giving an hour, minute and the second value.
- After using the TIME on Excel, once you get a valid time, you can easily format it as per your requirements.
TIME Function in Excel Video
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 –