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
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.
- 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?
- You can 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 the TIME option under the DATE and TIME function in the Excel menu.
How to Use TIME in Excel Sheet?
Take a look at the below-given examples of TIME in the ExcelExamples Of TIME In The ExcelTo add time in Excel, the user can directly enter the time values in the hh:mm format into the cell. Remember that the time entered in the cell should not exceed 24 hours. For instance, 5.30 in the evening is written as 17:30. 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.
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.
Note- To attain the result, you must enter the TIME formula in all three columns, D, E, and F cells.
Consider the screenshots of the above examples for a clear understanding.
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
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 FormulaSubtract Time In Excel FormulaFor subtraction of time values less than 24 hours, we can easily subtract using the ‘-’ operator. However, the time values that on subtraction exceed 24 hours/60 minutes/60 seconds are ignored by Excel. Therefore, we use a custom number format in such cases.
- Timeline ChartTimeline ChartIn project management terms, an Excel timeline chart is also known as a Milestone chart. It is used to visually track all stages of a project and assists a project manager in delivering the project to the end-user as committed before the start of the project.
- DAY FunctionDAY FunctionIn Excel, the DAY function calculates the day value from a given date. This function accepts a date as an argument and returns a two-digit numeric value as an integer value representing the given date's day. The formula to use this function is =Edate(serial number).
- TODAY Excel FunctionTODAY Excel FunctionToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time.
- Search For Text in ExcelSearch For Text In ExcelExcel provides the functionality that helps you to search for text in the file. You can use Find and Search function to search for a specific text cell value and arrive at the result.