Excel TIME Function returns the valid time with hours, minutes, and seconds value.
TIME Function: A Brief
The Excel TIME Function is used to create or return a valid Excel time value from individual hour, minute, and second values. This function is extremely helpful when you’re working with data that stores time in parts and you need to convert it into a proper Excel time format.
Objective
- Value Returned by Function
- Aim to return a valid Excel Time
- DAYS Function will return an Excel time with hour, minute, and second values.
TIME Function: A Syntax
=TIME(hour, minute, second) // Returns a proper Excel Time Value.
- hour: It should be a valid numerical input from 0 to 23, which represents the hour value.
- minute: It should be a valid numerical input from 0 to 59, which represents the minute value.
- second: It should be a valid numerical input from 0 to 59, which represents the second value.
The TIME Function will return the #NUM! error if any of the inputs are invalid or text or exceed the numerical input (32767) supported by Excel.
Note: The Excel TIME function will reset the time values if hour, minutes, second values exceeds the expected numerical inputs. For example:
=TIME(38,0,0)// The TIME function returns the value as 14:00:00 as the output.
Function Compatibility:
The Excel TIME Function was introduced in the very early versions of Excel. This function is supported by every modern version of Excel, including Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, and Microsoft 365.
Time Format in TIME Function:
The TIME function returns a valid time as a decimal number, but how Excel displays that result depends on the number format applied. Excel can show the result in different ways, such as AM/PM format, 24-hour format (like 13:45:00), or simply as a regular decimal number.
You can compare how these formats look by applying different number formats to separate cells or columns, just like in an example where columns E, F use different time formats.
Time Function with custom time formatting the output values.
Basic Examples of Using the TIME Function:
Examples Explanation:
- In Example 1, the input values for hour, minute, and second are all within the normal range, so the TIME function returns a valid time.
- In Example 2, the minute value exceeds the normal range (which should be between 0 and 59). The TIME function automatically adjusts this by adding 1 hour and 28 minutes and returns the updated time.
- In Example 3, both the hour and minute values go beyond their limits. The TIME function moves into the next day and gives the adjusted time result.
- In Example 4, the minute and second values are negative, so the TIME function shifts the time backward accordingly and returns a valid time.
- In Example 5, the minute value is over 60, so it is converted into 1 extra hour and 20 minutes, and the final adjusted time is returned.
- In Example 6, the values for hour, minute, and second all exceed their normal ranges. The TIME function processes the values, makes the necessary adjustments, and returns a valid time.
- In Example 7, the second’s value is huge and exceeds the supported range many times. The maximum value supported by Excel is “32,767,” and the given second value is 3348, which is actually less than the highest value supported by Excel. So, the TIME function will return the final time value by considering 3348 seconds as the equivalent 55 mins, 48 secs value.
- In Example 8, the second value is greater than 32,767, which is beyond Excel’s allowed limit. So, the TIME function returns a #NUM! error.
Using TIME Functions With Other Nested Functions:
That’s it. The article was originally published on: How to use Excel TIME Function?