Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel Convert Seconds to hh mm ss (7 Easy Ways)

Have you ever thought about how to convert seconds to hh mm ss format in Excel? Tired of searching for it online? But can’t find the right answer anywhere? If the answer to all the above questions is yes, then you’ve come to the right place. Here, we will take you through 7 easy and convenient methods to convert seconds to hh mm ss format in Excel. Hopefully, there will be no need to go anywhere else after that.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


7 Methods to Convert Seconds to hh mm ss in Excel

For clarification, let’s say we have a Sudoko Competition – Completion Time file in our hands. This dataset contains ID, Participant Names, and Completion Time (sec) in columns B, C, and D respectively.

excel convert seconds to hh mm ss

Now, we’ll convert these times in seconds to hh mm ss format using different approaches in Excel.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Using Format Cells Option to Convert Seconds to hh mm ss in Excel

In our first method, we’ll show the simplest way to convert seconds to hh mm ss format in Excel. So, let’s begin.

📌 Steps:

  • At first, create a new column Time in hh:mm:ss Format in Column E.

Using Format Cells Option to Convert Seconds to hh mm ss in Excel

  • Secondly, select cell E5 and write down the following formula.
=D5/86400

Here, D5 represents the Completion Time (sec) of the first competitor Robin. We divided the value of cell D5 by 86400. Because, 1 day = (24 × 60 × 60) = 86400 seconds. Basically, we converted the time in seconds to a fraction of a day.

  • Then, press ENTER.

  • At this moment, press CTRL + 1 to open the Format Cells dialog box.
  • In the dialog box, go to the Number tab.
  • Then, select Custom from the Category list.
  • After that, write down hh:mm:ss in the Type box.
  • Here, we can see the Sample like in the image below.
  • Later, click OK.

Using Format Cells Option

  • Now, the value in cell E5 looks like that.

  • At this point, bring the cursor to the bottom right corner of cell E5. Thus, it’ll look like a plus (+) sign. It’s the Fill Handle tool.
  • Now, drag the Fill Handle up to cell E14.

Using Fill Handle Tool

  • Thus, the remaining cells in the E5:E14 range get the desired results.

Using Format Cells Option to Convert Seconds to hh mm ss in Excel

Read More: Convert Seconds to Hours and Minutes in Excel (4 Easy Methods)


2. Inserting CONVERT Function to Convert Seconds to hh mm ss in Excel

If you’re one of those people who enjoy using Excel formulas, then our next methods have you covered. Just follow these simple steps.

📌 Steps:

  • At the very beginning, select cell E5 and paste the following formula into the Formula Bar.
=CONVERT(D5,"sec","day")

In the above formula, D5 represents the number argument, and the “sec” and “day” point to the from_unit and to_unit arguments respectively. Here, the CONVERT function converts the 1250 seconds to a fraction of days.

  • After that, tap the ENTER key.

Inserting CONVERT Function to Convert Seconds to hh mm ss in Excel

  • Presently, change the format of cell E5 like Method 1.
  • Then, double-click on the Fill Handle tool.

  • Hence, the remaining cells get filled by the relevant results.

Inserting CONVERT Function to Convert Seconds to hh mm ss in Excel

Read More: How to Convert Minutes to Hours and Minutes in Excel


3. Applying TEXT Function to Convert Seconds to hh mm ss in Excel

If you are curious to learn about the TEXT function in Excel, this method may come in handy. It’s simple and easy, just follow along.

📌 Steps:

  • Firstly, go to cell E5 and put the following formula into the cell.
=TEXT(D5/86400,"hh:mm:ss")

In this expression, the D5/86400 is the value argument while the “hh:mm:ss” represents the format_text argument. Here, the TEXT function converts the time in seconds in the D5 cell to time in hh:mm:ss format.

  • Secondly, hit the ENTER key.

Applying TEXT Function to Convert Seconds to hh mm ss in Excel

Read More: How to Convert Minutes to Seconds in Excel (2 Quick Ways)


4. Utilizing CHOOSE, MATCH, TEXT Functions

For those of you who want to learn about more techniques, there is another way to convert seconds to hh mm ss format in Excel. In this method, we’ll apply the TEXT, CHOOSE and MATCH functions to convert the time in seconds to the desired format. Let’s see the process in detail.

📌 Steps:

  • Initially, select cell E5 and insert the formula below.
=TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),":ss","m:ss","[h]:mm:ss"))

In the above formula, the D5 cell refers to the Completion Time (sec).

Formula Breakdown

  • MATCH(D5,{0,60,3600},1) → The MATCH function returns the relative position of an item in an array matching the given value. Here, D5 is the lookup_value argument that refers to the Completion Time. Following, {0,60,3600} represents the lookup_array argument from where the value is matched. Lastly, 1 is the optional match_type argument which indicates the Less than
    • Output2
  • CHOOSE(MATCH(D5,{0,60,3600},1),”:ss”,”m:ss”,”[h]:mm:ss”) → becomes
    • CHOOSE(2,”:ss”,”m:ss”,”[h]:mm:ss”) → The CHOOSE function chooses a value or action to perform from a list of values,base on an index number. Here, 2 is the index_num argument while“:ss”,”m:ss”,”[h]:mm:ss” represents the value1, value2, and  value3 According to index number 2, the function chooses the “m:ss” format.
    • Output“m:ss”
  • TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),”:ss”,”m:ss”,”[h]:mm:ss”)) → becomes
    • TEXT(D5/86400,”m:ss”) → converts a value to text in a specific number format. Here, D5/86400 is the value argument while “m:ss” represents the format_text The function converts the value of 0.01446 to h:mm:ss format.
    • 1250/8640001446
    • Output20:50
  • Subsequently, press ENTER.

Utilizing CHOOSE, MATCH, TEXT Functions

Read More: How to Convert Seconds to Minutes in Excel


5. Employing INT and ROUND Functions

As with Excel, there are multiple ways of doing the same task. Hence, you can perform the solution in another way. Let’s explore the method step by step.

📌 Steps:

  • Initially, select cell E5 and paste the following formula.
=INT(D5/3600)&":"&INT(((D5/3600)-INT(D5/3600))*60)&":"&ROUND((((D5/3600)-INT(D5/3600))*60 - INT(((D5/3600)-INT(D5/3600))*60))*60,0)

Here, the D5 cell refers to the Completion Time in seconds.

Formula Breakdown

  • INT(D5/3600) → The INT function rounds a number to the nearest integer. Here, the D5 cell points to the Completion Time in seconds which is divided by 3600 since there are 3600 seconds in 1 Thus, we get the time in hours.
    • Output0
  • INT(((D5/3600)-INT(D5/3600))*60) → In this formula, we obtain the minutes part by subtracting INT(D5/3600) from the D5/3600 and multiplying the answer by 60 since there are 60 minutes in 1 The INT function returns only the integer part of the answer.
    • 3472-00.3472
    • 3472*6020.833
    • Output 20
  • ROUND((((D5/3600)-INT(D5/3600))*60 – INT(((D5/3600)-INT(D5/3600))*60))*60,0) → rounds a number to a specified number of digits. In this expression, we calculate the seconds part in a similar way. The ROUND function rounds the answer to zero decimal places i.e. it returns only the integer part of the answer.
    • 833-200.833
    • 833*6050
  • Lastly, use the Ampersand (&) operator to combine the hours, minutes, and seconds.
  • Lastly, press the ENTER button.

Employing INT and ROUND Functions

Read More: How to Convert Minutes to Tenths of an Hour in Excel (6 Ways)


6. Implementing MOD, TEXT, and TRUNC Functions

In this method, we’ll combine some functions to perform the task. So, without further delay, let’s dive in!

📌 Steps:

  • Primarily, jump to cell E5 and paste the following formula into the cell.
=TRUNC(D5/3600)&TEXT(MOD(D5/86400,1),":mm:ss")

Here, the MOD function takes D5/86400 as the number argument and 1 as the divisor argument. It returns 0.01446 which is the value argument of the TEXT function. The TEXT function converts the format of the text as “:mm:ss” which is the format_text argument of the above function. TRUNC(D5/3600) gives 0 as result. Because the TRUNC function returns the integer part of a number.

  • Correspondingly, hit ENTER.

Implementing MOD, TEXT, and TRUNC Functions

Read More: How to Convert Milliseconds to Seconds in Excel (2 Quick Ways)


7. Engaging VBA Code

You might be wondering, is there a way to automate this task? Then VBA has you covered. Just follow along.

📌 Steps:

  • At first, construct a new column Time in hh:mm:ss Format under Column E like Method 1.
  • Secondarily, go to the Developer tab.
  • Then, select Visual Basic on the Code group.
  • Alternatively, press ALT + F11 to do the same task.

Engaging VBA Code to Convert Seconds to hh mm ss in Excel

  • Instantly, the Microsoft Visual Basic for Applications window opens.
  • Afterward, move to the Insert tab.
  • Later, select Module from the options.

  • Immediately, it opens the Code Module.
  • Then, write down the following code in the Module.
Sub convert_sec()
Dim secs As Integer, converted_time As Date
For x = 5 To 14
secs = Cells(x, 4).Value
converted_time = secs / 86400
Cells(x, 5).NumberFormat = "hh:mm:ss"
Cells(x, 5).Value = converted_time
Next x
End Sub

Engaging VBA Code to Convert Seconds to hh mm ss in Excel

Code Breakdown

Sub convert_sec()
Dim secs As Integer, converted_time As Date

  • Firstly, we create the macro and give it the name convert_sec.
  • Then, we defined the two variables.

For x = 5 To 14
secs = Cells(x, 4).Value
converted_time = secs / 86400
Cells(x, 5).NumberFormat = “hh:mm:ss”
Cells(x, 5).Value = converted_time
Next x

  • After that, we insert a for loop for the value of 5 to 14 for x.
  • Later, we assigned the value of cell D5 to the secs variable.
  • Next, divide the variable secs by 86400 and assign the value to converted_time variable.
  • At this moment, change the format of cell D5 to hh:mm:ss format.
  • Subsequently, put the above variable to cell E5.
  • Finally, move to the lower cell D6 and continue the above loop till going to cell D14.
  • Lastly, select the Run icon or press F5 on the keyboard.

  • Now, return to the VBA worksheet.
  • Thus, the Time in hh:mm:ss Format column gets filled automatically with the correct results.

Engaging VBA Code to Convert Seconds to hh mm ss in Excel

Read More: Convert Decimal Coordinates to Degrees Minutes Seconds in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

Thank you for reading this article. I hope all of the methods mentioned above to convert seconds to hh mm ss format in Excel will now prompt you to apply them in your Excel spreadsheets more effectively. Don’t forget to download the Practice file. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo