In this post, I’ll share with you a formula that will help you convert seconds to the HH:MM:SS format in Google Sheets. With this formula, you’ll be able to easily convert elapsed time into a more readable and understandable format.
The Struggle with Formatting
You might think that simply formatting the cell or using the TEXT function will give you the desired result. Unfortunately, that’s not the case. Formatting won’t work, and the TEXT formula won’t convert the total seconds to the required time format.
For instance, if you have the elapsed time in seconds (let’s say 100,000) in cell C2 and try formatting it using the duration or time format options, you’ll end up with incorrect results.
The Solution: Dividing by 86,400
To get the correct duration in the HH:MM:SS format, you need to divide the total seconds by 86,400 (60 seconds 60 minutes 24 hours). Once you’ve done that, format the cell to display the duration, and voila! You’ll have the correct duration displayed.
Here’s an example:
If you select the time format instead of duration, you’ll get 03:46:40, but that skips the 24 hours (day), which is not correct.
The Formula: Step-by-Step Guide
If you want to convert seconds to the HH:MM:SS format in Google Sheets, follow these steps:
Step 1: Use the TRUNC formula in cell D5 to get the number of days from the total seconds in cell C2.
=trunc(C2/86400)Step 2: Use the MOD + TEXT formula in cell D6 to get the remaining seconds in time format after converting the total seconds to days (from step 1).
=text(mod(C2/86400,1),"HH:MM:SS")Step 3: Combine these two formulas in cell D2 and add the text “days” in between. You can now empty cells D5 and D6 too.
=trunc(C2/86400)&" days "&text(mod(C2/86400,1),"HH:MM:SS")By following these steps, you’ll have a formula that successfully converts seconds to the HH:MM:SS time format in Google Sheets.
Dealing with Output Issues
There are a couple of visible issues with this formula. For example, if the total elapsed seconds to convert are only 60, the formula would incorrectly convert it to 0 days 00:01:00 instead of just 00:01:00.
To clean up the output, we can use the IFS logical function to check the number of days underlying in the total elapsed seconds. By incorporating the IFS function, we can handle different scenarios more effectively.
Here’s how the final formula looks like in cell D2:
=ifs(trunc(C2/86400)<=0,,trunc(C2/86400)=1,trunc(C2/86400)&" day",trunc(C2/86400)>1,trunc(C2/86400)&" days")&" "&text(mod(C2/86400,1),"HH:MM:SS" )And that’s it! You now have a formula that converts seconds to the HH:MM:SS format in Google Sheets. Enjoy!
