INTRODUCTION
TIME FUNCTION comes under the DATE AND TIME FUNCTIONS category in Excel.
TIME FUNCTION simply returns the decimal equivalent for a particular time.
The range for the time function is from the 0 seconds i.e. 00:00 [12 am] upto the 0.99988426 that is 23 hrs 59 min 59 seconds.
THIS FUNCTION IS VERY HELPFUL IN MANY TRICKS TO FIND OUT VARIOUS VALUES USING THE DATE AND TIME FUNCTION.
PURPOSE OF TIME FUNCTION IN EXCEL
TIME FUNCTION returns the equivalent decimal number of a particular time.
For example, If we know the hours, minutes and seconds we can find out its decimal number by the use of this function.
It’ll become more clear when we solve the examples later in this article.
PREREQUISITES TO LEARN TIME FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the HANDLING OF DATE AND TIME BY EXCEL.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: TIME FUNCTION
The Syntax for the function is
=TIME(HOUR, MINUTE, SECONDS)
HOUR It is the number of hours. Put the value from 0 to 23.
MINUTE It is the number of minutes. Put the value in between 0 and 59.
SECOND It is the number of seconds. Put the value in between 0 and 59.
Although all the three mentioned arguments can accept the value upto 32767 and it won’t complain by giving you an error but it’ll make some adjustments.
For example, If we give the value of the hours as 27, it’ll divide it by 24 i.e. one day and keep the rest 3 hours as the time.
Similarly if minutes are 61, it’ll divide it by 60 , create one hours and the rest one minute is left.
On the same lines, if we put the seconds as 75, it’ll create one minute and rest 15 seconds are treated as Seconds only.
EXAMPLE:TIME FUNCTION IN EXCEL
DATA SAMPLE
For better understanding, let us create some time using the TIME FUNCTION with various cases.
| EXAMPLE | HOUR, MIN, SECONDS |
| 1 | 10,15,49 |
| 2 | 12,54,41 |
| 3 | 26,42,12 |
| 4 | 23,87,98 |
Example number 3 and 4 have the absurd values which are greater than the limit. We’ll see how the TIME FUNCTION tackles such situation.
STEPS TO USE TIME FUNCTION
The picture above shows the input, result and the formula used.
Let us discuss them one by one.
EXAMPLE 1 and EXAMPLE 2 are the simple usage of the TIME Functions.
The Formula used are simply TIME(HOUR, MINUTE, SECOND).
The formula used for the EXAMPLE 1 and EXAMPLE 2 are =TIME(10,15,49) for Example 1 and =TIME(12,54,41) for Example 2
EXAMPLE 3 has the input as 26, 42,12 which means 26 hours, 42 minutes and 12 seconds.
As we already discussed, if the hours are more than 23 i.e. 24 or more, it’ll be divided by the 24 hours and remainder will be taken. So if we divide 26 by 24, 2 comes out as remainder which can be confirmed in the result .
The minutes and seconds are well within the range so output becomes 2:42 AM.
EXAMPLE 4 is the similar case of out of range values and has the inputs as 23,87,98.
In this we have hours in the range but minutes and seconds are out of range. So first of all seconds will be converted into minutes.We have 98 seconds which will be equal to 1 minute and 38 seconds.
This one minute will be added to the minutes which are 87 , so the minutes become 88.88 minutes means 1 hour and 28 minutes.
So our final time becomes 24 hours 28 minutes and 38 seconds.The result is 12:28 AM which is correct.
THE SECONDS ARE NOT VISIBLE DUE TO THE FORMAT OF THE CELL.IF WE CHANGE THE FORMAT , SECONDS WILL BE VISIBLE TOO.
CONFUSION CLARIFICATIONS
DIFFERENCE BETWEEN TIMEVALUE FUNCTION AND TIME FUNCTION
If you have knowledge about both of the functions[ TIME VALUE FUNCTION and TIME FUNCTION], which almost do the same task, it can create confusion why we need the two different functions.
So we can see clearly that both of them take the different arguments, although the output is same.
TIMEVALUE takes the time as TEXT whereas TIME FUNCTION takes the hour, minutes and seconds separately.Both of these functions are used in the different scenarios.Although, if we have hour, minutes and seconds we can create a string and pass it to TIMEVALUE and it’ll provide the answer.
TIME has the flexibility of adjusting the hours, minutes and seconds. Its power is recognized when we need to create the slots and perform rounding of time.