What it does – It converts seconds to HH:MM:SS format
Syntax:=TRUNC(cell/3600)&TEXT(MOD(cell/86400,1), ":MM:SS")
Sample usage:=TRUNC(100000/3600)&TEXT(MOD(100000/86400,1), ":MM:SS")
//It converts 100,000 seconds to hours (HH:MM:SS) format
Sample Google Sheets template with formula here.
There are multiple instances where one needs to convert a data value from one scientific unit to another. Therefore, it’s important to know how to convert from one scientific unit to another. In this tutorial, our prime objective is to learn how to convert seconds to hours in Google Sheets.
Being able to convert seconds to hours in Google Sheets may seem to be something tricky but in reality, is quite simple. We will learn how to convert seconds to HH:MM:SS format in Google Sheets. This format is quite easy to interpret and is used most widely in digital clocks.
Prerequisites before implementation
Before beginning the tutorial, we need to learn about a few functions that will be used in our formula. Understanding these functions will give you a better grasp of the tutorial.
TRUNC function in Google Sheets
TRUNC function in Google Sheets performs truncation of the numbers. The syntax of the TRUNC function is as follows:
=TRUNC(x, y)A brief description of the syntax is as follows:
- X: It stands for the number which we want to display
- Y: It stands for the number of decimal places that we want to display
For example, Let’s say the value of x is 45.890312 and the value of y is 3. In that case, it means that we wish to display x up to 3 decimal places only. The number displayed as the final output will be 45.890.
MOD function in Google Sheets
MOD function is used to display the remainder of the numbers. The syntax of the MOD function is as follows:
=MOD(x, y)A brief description of the syntax is as follows:
- X: It stands for the number that you want to divide. It’s also known as the dividend
- Y: It stands for the number that you want to divide with. It’s also known as the divisor
For example, let’s say that the value of x is 70 and the value of y is 8. We apply the MOD function. In that case, the output will be 6. This is because when we divide 70 by 8 then the remainder is 6.
TEXT function in Google Sheets
TEXT function is used to display a number in a particular format. The syntax of the TEXT function is as follows:
=TEXT(x, y)A brief description of the syntax is as follows:
- X: It stands for the number that we want to convert to a particular format
- Y: It stands for the format to which we want to convert the number. In this case, we will use “:MM:SS” format.
For example, Let’s say the value of x is 25 and the value of y is “$0.00”. X will be displayed as “$25.00”.
We will now start learning about how to convert seconds to hours in Google Sheets.
Convert seconds to hours in Google Sheets
We are now equipped with the knowledge of various functions which we will use. In the following example, we have given time in seconds format and our objective is to convert it to HH:MM:SS format in Google Sheets.
In this tutorial, we will convert 100,000 seconds to HH:MM:SS format. The steps are as follows:
- Select the empty cell under HH:MM:SS header
- Begin your function with the ‘=’ sign. Type in the ‘TRUNC’. The Google Sheets will prompt this function, press the Tab/Enter key to autocomplete. The tooltip guide will appear along with the details.
- Type in the cell address of the time given in seconds. In this case, it will be A4. Divide it by 3600 as there are 3600 seconds in an hour and then close the TRUNC function as shown
- Add the ‘&’ operator (to join another function) after the TRUNC function and type in ‘TEXT’ to call the TEXT function
- Inside the TEXT function, we will be calling the MOD function so type in ‘MOD’ as the parameter. The function written till now should look as follows:
- Inside the MOD function enter the cell address of the time given in seconds format. In this case, it will be A4 and divide A4 by 86400 as there are 86400 seconds in a day and then enter 1 as the second parameter of the MOD function. The MOD function is now complete. The function should look like this:
=TRUNC(A4/3600)&TEXT(MOD(A4/86400,1)
- Use a comma as a separator and enter the next parameter as “:MM:SS” and close the parentheses. The final formula should look something like this:
=TRUNC(A4/3600)&TEXT(MOD(A4/86400,1), ":MM:SS")
- Hit Enter key and it will display time in HH:MM:SS format
We have successfully learned how to convert seconds to hours in Google Sheets. The explanation of the whole procedure is as follows:
- We used the following formula to convert seconds to hours in Google Sheets i.e display time in HH:MM:SS format
=TRUNC(A4/3600)&TEXT(MOD(A4/86400,1), ":MM:SS")- We use the TRUNC function to find out the number of hours. We divided the seconds by 3600 as an hour has 3600 seconds and then we combined it with the other part of the function using the ‘&’ operator
- The TEXT function is used to format the output of the MOD function into minutes and seconds
- The MOD function separates minutes from the given time in seconds by simply dividing it by 86400 because a day has 86400 seconds
This is the logic that we used to perform the steps to convert seconds to hours in Google Sheets. It’s quite simple and effective.
Conclusion
We have successfully learned how to convert seconds to hours in Google Sheets. We have gone through a step-by-step process along with a detailed explanation of the functions involved in the complete process. You can now easily convert seconds to hours in Google Sheets.
See Also
You have successfully learned how to convert seconds to hours in Google Sheets. Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you save time and effort.
We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.
Here are some articles you might be interested in:
https://blog.tryamigo.com/how-to-create-a-countdown-timer-in-google-sheets/
https://blog.tryamigo.com/sort-query-using-order-by-in-google-sheets/
https://blog.tryamigo.com/introduction-to-date-function-in-google-sheets/