Have you ever needed to create a time value from hours, minutes, and seconds in Google Sheets? Look no further than the TEMPS function! This powerful tool allows you to generate a time value, which is a decimal number representing the time of day. Let’s dive in and explore how to use it.
Syntax of the TEMPS Function in Google Sheets
First, let’s take a look at the syntax of the TEMPS function:
=TEMPS(hour, minute, second)The hour, minute, and second are the respective values of the hour, minute, and second that you want to combine into a time format. You can either input these values directly into the formula or reference the cells containing the values. Remember that the hour value must be between 0 and 23, the minute value between 0 and 59, and the second value between 0 and 59.
Examples of the TEMPS Function in Google Sheets
Using the TEMPS Function with Whole Numbers
The TEMPS function can utilize whole number values stored in separate cells. Simply specify the cell references within the function.
- Identify the cells containing the hour, minute, and second values.
In our example, the hour values are stored in column A, the minute values in column B, and the second values in column C.
- Enter the formula
=TEMPS(hour, minute, second)in an empty cell.
For example, in row 2, we will use the following formula:
=TEMPS(A2, B2, C2)And we will place it in cell D2 under the “hour” heading.
- Press Enter.
You can drag the formula down the column using the blue square in the bottom right corner of the cell or apply auto-completion suggestions (if available) to convert other rows using the TEMPS function.
Using the TEMPS Function with Decimals
Using the TEMPS function with decimals is similar to using whole numbers, except that when decimals are present, they are discarded, and only the whole numbers are converted into time.
- Identify the hour, minute, and second values.
In our example, the hour values are stored in column A, the minute values in column B, and the second values in column C.
- Enter the formula
=TEMPS(hour, minute, second)in an empty cell.
The formula will be:
=TEMPS(A2, B2, C2)And we will place it in the cell in column D.
- Press Enter.
You can drag the formula down for other rows or apply auto-completion suggestions (if available) to convert other values using the TEMPS function.
Using the TEMPS Function for Values Greater Than 24 Hours
When the hour value exceeds 24, the TEMPS function resets the count to 0 and then converts it into the corresponding time. For example, if the hour value is 25, the TEMPS function will display 1:00 AM.
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 as follows:
=ROUND(hour/24)Here are the steps to convert the values into time and count the number of days:
- Identify the cells containing the hour, minute, and second values.
In our example, the hour values are stored in column A, the minute values in column B, and the second values in column C.
We have created two columns, D and E, for the day and hour, respectively.
- Set up the formula for the day
=ROUND(hour/24)
For the first row of our data set, the formula becomes:
=ROUND(A2/24)And we place the formula in cell D2.
- Set up the TEMPS formula
=TEMPS(hour, minute, second)
Now we can set up the hour formula:
=TEMPS(A2, B2, C2)And we place it in cell E2.
If you want to apply the formulas to multiple rows, you can drag the formula down or use auto-completion suggestions (if available) for the other rows.
Using the TEMPS Function When Minutes or Seconds Exceed 60
If the minute and/or second value exceeds 60, the TEMPS function automatically adjusts the count for the other values:
- If the second value exceeds 60, the TEMPS function adds 1 to the minute value.
- If the minute value exceeds 60, the TEMPS function adds 1 to the hour value.
Here’s how to do it:
- Identify the hour, minute, and second values.
In our example, we have three rows:
- Row 2 has a minute value greater than 60.
- Row 3 has a second value greater than 60.
- Row 4 has both a minute and second value greater than 60.
The hour values are stored in column A, the minute values in column B, and the second values in column C.
- Create the formula
=TEMPS(hour, minute, second)
The formula becomes:
=TEMPS(A2, B2, C2)And we add it to cell D2.
- Press Enter.
Notice how the hour value is adjusted due to the minute value exceeding 60.
You can drag the formula down or use auto-completion suggestions (if available) for the other rows.
We hope this article has helped you better understand the TEMPS function in Google Sheets. You might also enjoy our articles on subtracting dates in Google Sheets and using the UNIQUE function in Google Sheets. To streamline your workflow, we recommend reading our guide on how to attach Google Sheets to an email and trying out our expiration reminder software!
