How to Convert Seconds to HH:MM:SS Format in Google Sheets

If you have a list of durations in seconds and want to display them in a readable HH:MM:SS format, Google Sheets makes it easy with a formula that returns an actual time value—not plain text.

This means you can still sort, filter, and perform calculations with the result. Whether you’re tracking time for workouts, video clips, or task durations, converting seconds into hours, minutes, and seconds will make your data more useful and understandable.

In this tutorial, I’ll show you how to convert seconds to HH:MM:SS in Google Sheets, step by step.

Formula to Convert Seconds to Time in the Format HH:MM:SS in Google Sheets

To convert seconds to time in Google Sheets, use the following formula:

=QUERY(MOD(A1/86400, 1), "SELECT Col1 FORMAT Col1 'hh:mm:ss'")
  • Replace A1 with the cell reference containing your duration in seconds.

If the value in A1 is greater than or equal to 86,400 (the number of seconds in a day), you may also want to calculate the day component separately using:

=TRUNC(A1/86400)

This will return the number of full days in the given duration.

We’ll go over how this formula works after a few practical examples.

Examples of Converting Seconds to Time

Assume you have the following durations in column A (starting from cell A2):

Duration in Seconds
60
61
120
88

To convert these seconds to HH:MM:SS in Google Sheets, enter the following formula in cell B2 and drag it down:

=QUERY(MOD(A2/86400, 1), "SELECT Col1 FORMAT Col1 'hh:mm:ss'")

Your output in column B will look like this:

Example showing how to convert seconds to HH:MM:SS time format in Google Sheets using a formula

Optionally, if you want to extract the day component (in case some durations are longer than 24 hours), enter the following formula in cell C2 and drag it down:

=TRUNC(A2/86400)

For example, if the value in A2 is 150000 seconds, the formulas would return:

  • Time (QUERY): 17:40:00
  • Days (TRUNC): 1

How Does the Formula Work?

Let’s break it down:

  • A2/86400
    Divides the seconds by the number of seconds in a day (86,400) to get the equivalent duration as a decimal fraction of a day.
  • MOD(A2/86400, 1)
    Strips out the integer part (days), leaving only the decimal part which represents the time of day.
  • QUERY(..., "SELECT Col1 FORMAT Col1 'hh:mm:ss'")
    Converts the decimal value to the HH:MM:SS time format while keeping it as a valid time value (not plain text). We use QUERY instead of TEXT to maintain a time-formatted number that remains usable in further calculations.

This method is especially useful if you want to perform additional operations on the time values, such as adding durations or comparing times.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.