The TIME function in Google Sheets is used to create a time value from hours, minutes and seconds. The function returns a time value, which is a decimal number representing the time of day.
The syntax for the TIME function is:
=TIME(hour, minute, second)
Where
Hour, minute, and second are for the given hour, minute, and second to be merged to a time format. They can be either inserted inside the formula or you can set the reference to the cell containing the values.
Note:
The hour value should be between 0 and 23
the minute value should be between 0 and 59,
and the second value should be between 0 and 59.
The TIME function can use whole number values stored in separate cells. Simply specify the cell references within the function.
Example: A sheet containing the hour, minute, and second values are stored as follows:
The hour values are stored in Column A; the minute values are stored in Column B; and the second values are stored in Column C.
In our example the input values in row 2 we will use for TIME function are the following:
Hour: A2
Minute: B2
Second: C2
In our example we will set up the formula for row 2 as:
=TIME(A2,B2,C2)
And insert it to the cell D2 under the time heading.
Alternatively, you can do the following:
1. Type =TIME( in cell D2
2. Click A2, B2, and C2 in order while hold-pressing the Ctrl button (Command on Mac)
3. Type )
You can drag the formula down the column with the blue square in the bottom right hand corner of the cell or apply Autofill suggestion (if it appears) to also convert the other rows using TIME function:
Using the TIME function with decimals is similar to whole numbers except when decimals are present it removes the decimals and only converts the whole numbers to time:
In our example, the hour values are stored in Column A; the minute values are stored in Column B; and the second values are stored in Column C.
For Row 2 that means that the references to the input values for TIME function are the following:
Hour: A2
Minute: B2
Second: C2
The formula becomes:
=TIME(A2,B2,C2)
And insert it to the cell in column D:
Alternatively, you can do the following:
1. Type =TIME( on cell D2
2. Click A2, B2, and C2 in order while hold-pressing Ctrl button
3. Type )
You can can fill down the formula for the other rows or apply Autofill suggestion (if it appears) to also convert the other values using the TIME function:
When the hour value is more than 24, the function resets the count back to 0 and then converts it to its corresponding time. This means that if the hour value was 25, the TIME function would display 1:00 AM.
This is usually 12:00 AM for the 12-hour scheme.
To keep track of the number of days, you can add another column for the day count. The formula to calculate the number of days from the number of hours is:
=INT(hour/24)
The following steps will detail how to convert the values to time and also count the number of days:
In our Example:
The hour values are stored in Column A;
the minute values are stored in Column B;
and the second values are stored in Column C.
We set two columns D and E for the day and time, respectively.
For Row 2 that means that the cell references for the TIME function formula will be:
Hour: A2
Minute: B2
Second: C2
For the first row in our data set, the formula becomes:
=INT(A2/24)
And we insert the formula into cell D2:
Alternatively, you can do the following:
1. Type =INT( in cell D2
2. Click A2
3. Type /24)
4. Press Enter
We can now set up the time formula:
=TIME(A2,B2,C2)
And insert it to the cell E2:
Alternatively, you can do the following:
1. Type =TIME( on cell D2
2. Click A2, B2, and C2 in order while hold-pressing Ctrl button
3. Type )
If you want to apply the formulas to several rows, you can drag the formula down to the other rows or apply the Autofill suggestions if they appear.
If the minute and/or second value is more than 60, the function automatically adjusts the count of other values:
If the second value is more than 60, then the TIME function adds 1 to the minute value.
If the minute value is more than 60, then the TIME function adds 1 to the hour value.
In our example we have three rows:
Row 2 has a minute value greater than 60;
Row 3 has a second value more than 60;
Row 4 has both minute and second values greater than 60.
The hour values are stored in Column A;
the minute values are stored in Column B;
and the second values are stored in Column C.
For Row 2 in our example the input values for TIME formula are:
Hour: A2
Minute: B2
Second: C2
The formula becomes:
=TIME(A2,B2,C2)
We will add it to cell D2.
Alternatively, you can do the following:
1. Type =TIME( in cell D2
2. Click A2, B2, and C2 in order while pressing and holding the Ctrl button (Command on a Mac)
3. Type )
Note how the hour value was adjusted due to minute value exceeding 60:
You can drag down the formula or apply the Autofill suggestion (if it appears) for the other rows:
We hope this article has helped you and given you a better understanding of the Google Sheets TIME function. You might also like our articles on how to subtract dates in Google Sheets and how to use the Google Seets UNIQUE function.
To optimize your workflow, we recommend reading our guide on how to attach Google Sheets to an email.
