How to Use Excel TIME Function?
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 | TIME Function will return a Excel time with hour, minute, 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 representing the hour value.
- minute: It should be a valid numerical input from 0 to 59 which representing the minute value.
- second: It should be a valid numerical input from 0 to 59 which representing the second value.
The TIME Function will return #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 has been 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, 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.
Basic Examples of Using TIME Function:
| S.no | Hour | Minute | Second |
|---|---|---|---|
| 1 | 2 | 16 | 8 |
| 2 | 7 | 88 | 50 |
| 3 | 35 | 67 | 50 |
| 4 | 49 | -14 | -18 |
| 5 | 29 | 80 | 0 |
| 6 | 36 | 88 | 106 |
| 7 | 46 | 125 | 3348 |
| 8 | 33 | 71 | 32768 |
| S.no | Final Result | Time Formatted Results | Raw Value of output | Comments |
|---|---|---|---|---|
| 1 | 02:16:28 | 02:16 AM | 0.0948 | All values within normal range and so it returns standard output. |
| 2 | 08:28:50 | 08:28 AM | 0.3534 | Minutes exceed (88), TIME adjusts by adding 1 hour and 28 mins. |
| 3 | 12:07:50 | 12:07 PM | 1.5054 | Both hour and minute exceed 24/60, result wraps into next day. |
| 4 | 00:45:42 | 12:45 AM | 2.0317 | Negative minute and second, TIME adjusts backwards correctly. |
| 5 | 06:20:00 | 06:20 AM | 1.2639 | Minute value over 60 and so converted to extra hour + 20 mins. |
| 6 | 13:29:46 | 13:29 | 1.5623 | All the inputs are exceeds and so TIME still calculates valid time after adjustments. |
| 7 | 01:00:48 | 01:00 | 2.0422 | Large second value (3348 seconds = 55 mins, 48 secs) but less than 32,767 |
| 8 | #NUM! | #NUM! | 1.8036 | Extremely large second value (32768 seconds ≈ 9 hours) which exceeds valid range, returns #NUM! error |
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 backwards 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 the Example 7, the second’s value is huge and exceed many times than the supported range. 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 the Excel. So the TIME function will returns 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:
| Hour | Minute | Second |
|---|---|---|
| 6 | 45 | 30 |
Formulas used along with TIME function are,
| S.no | Formula to | Formula Used | Result |
|---|---|---|---|
| 1 | Calculates the remaining time until midnight from the given hour, minute, and second. | =1 – TIME(B3,B5,B7) | 17:14:30 |
| 2 | Determines whether the given time is before or after noon (AM or PM). | =IF(HOUR(TIME(B3,B5,B7))<12,”AM”,”PM”) | AM |
| 3 | Flags whether the input time is early, exactly on time, or late based on a 9:00 AM reference. | =IF(TIME(B3,B5,B7)<TIME(9,0,0),”Early”, IF(TIME(B3,B5,B7)=TIME(9,0,0),”On Time”,”Late”)) | Early |
| 4 | Adds conditional work hours: 6 hours if time is after 2:00 PM, otherwise adds 5 hours. | =IF(TIME(B3,B5,B7)>=TIME(14,0,0), TIME(B3,B5,B7) + TIME(6,0,0), TIME(B3,B5,B7) + TIME(5,0,0)) | 11:45:30 |
| 5 | Calculates the number of minutes elapsed from the given time until now. | =ABS(INT((NOW() – (TODAY() + TIME(B3,B5,B7))) * 24 * 60)) | 00:00:00 |
| 6 | Categorizes the input time into Morning, Afternoon, or Night Shift based on standard shift hours. | =IF(AND(TIME(B3,B5,B7)>=TIME(6,0,0), TIME(B3,B5,B7)<TIME(14,0,0)), “Morning Shift”, IF(AND(TIME(B3,B5,B7)>=TIME(14,0,0), TIME(B3,B5,B7)<TIME(22,0,0)), “Afternoon Shift”, “Night Shift”)) | Morning Shift |
| 7 | Displays the current date and time in a readable format for time-stamping or logging purposes. | =TEXT(NOW(),”dd-mmm-yyyy hh:mm:ss AM/PM”) | 07-Apr-2025 01:03:48 PM |
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
Content References:
- Working with Time in Microsoft Excel | Time Functions and Conversion by Chris Menard, Retrieved on 07/04/2025: Link.
- Date and Time Functions in Microsoft Excel – For Beginners by Teacher’s Tech, Retrieved on 07/04/2025: Link.
- Insert the current date and time in a cell by Microsoft Support, Retrieved on 07/04/2025: Link.
- How to Use Time Functions in Excel by Dummies, Retrieved on 07/04/2025: Link.
- Time Functions in Excel Tutorial – TIME, HOUR, MINUTE, SECOND by Y. Acosta Excel Tutorials, Retrieved on 07/04/2025: Link.
