What is the Excel TIME Function?
The Excel TIME function is the opposite of the HOUR function, MINUTE function and SECOND function, assemble a valid excel TIME from the hour, minute and second components.
The Excel TIME function returns a decimal number from 0 (zero) to 0.999988425925926 representing the TIME from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM).
TIME Syntax
TIME(hour, minute, second)
- hour, required, integer number between 0 to
- If the number is less than 0 or greater than 32767, then return a #NUM! error.
- If the number is greater than or equal to 24, it will be divided by 24. The remainder is considered as the HOUR
- TIME(27,0,0) will be the same as TIME(3,0,0)
- minute, required, integer number between 0 to
- If the number is greater than 32767, then return a #NUM!
- You can fill the minute argument using a negative number, as long as there is a number in hour argument and there is still a reminder if reduced by MINUTE number. Otherwise, it will return a #NUM! error.
- If the number is greater than or equal to 60, it will be divided by 60, the result will add to the HOUR number, and the remainder will be considered as a MINUTE
- TIME(0,150,0) will be the same as TIME(2,30,0)
- second, required, integer number between 0 to
- If the number is greater than 32767, then return a #NUM error.
- You can use a negative number as a minute argument as long as there is a number in the hour or minute argument and if both reduced by the SECOND number, there is still a reminder. Otherwise, it will return a #NUM! error.
- If the number is greater than or equal to 60 then it will be divided by 60, the result adds a MINUTE number, and the rest is considered as a SECOND number.
- TIME(0,0,150) will be the same as TIME(0,2,30)
- TIME(0,0,9059) will be the same as TIME(2,30,59)
How to Use TIME Function in Excel
For example, there are data such as the image below. There are the Hour, Minute and Second columns; each column contains several numbers, some contain text. What is the result of the TIME function in column D that uses data in all three columns as the argument?
TIME Function #1
=TIME(A2,B2,C2)
Nothing special, the TIME function converts the HOUR, MINUTE and SECOND number to the valid excel TIME value. The result is 1:45:24 PM (the format can be different)
TIME Function #2, TIME Function #3
=TIME(A3,B3,C3)
=TIME(A4,B4,C4)
Both provide the same results, even though the HOUR numbers are different. As explained above, if the HOUR number is more than 24 then Excel will divide it by 24 and consider the remainder as the HOUR number.
39 divided by 24, the result is 1, the remaining is 15. Excel only takes into account the remaining results. TIME (39,0,0) equals to TIME (15,0,0)
TIME Function #4, TIME Function #5
=TIME(A5,B5,C5)
=TIME(A6,B6,C6)
Similar to the previous TIME function, HOUR and MINUTE numbers are different, but the results are the same. Unlike the HOUR number, which only takes into account the results for the rest. If the MINUTE number is greater than or equal to 60 will add to the HOUR number, the remainder is considered as a MINUTE number.
150 divided by 60, the result is 2, the remaining is 30. TIME (0,150,0) equals to TIME (2,30,0)
TIME Function #6, TIME Function #7
=TIME(A7,B7,C7)
=TIME(A8,B8,C8)
Like the TIME function #4 and TIME function #5. If the SECOND number is greater than or equal to 60, it adds a MINUTE number, if the MINUTE number reaches 60, it will increase the HOUR number.
The result is 9059 seconds equal to 2 hours 30 minutes 59 seconds.
TIME Function #8
=TIME(A9,B9,C9)
The SECOND number indeed is greater than 60 but no more than 32767. The result is a #NUM error!
TIME Function #9, TIME Function #10
=TIME(A10,B10,C10)
=TIME(A11,B11,C11)
Yes, you can use negative numbers too, with one condition the result cannot be negative.
The TIME function #9 means 2 hours minus 50 seconds the result is 1 hour 59 minutes 10 seconds, while the TIME function #10 cannot be reduced because the HOUR number is 0.
TIME Function #11, TIME Function #12
=TIME(A12,B12,C12)
=TIME(A13,B13,C13)
Yes, you can use text too for all three arguments, there will be no problem as long as Excel able converts it to numbers, otherwise, return a #VALUE! error.
The results of all TIME functions in column D can be seen in the picture below
