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.
Excel Convert Seconds to hh mm ss: 7 Easy Ways
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.
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.
- At first, create a new column Time in hh:mm:ss Format in Column E.
- 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.
- 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.
- Thus, the remaining cells in the E5:E14 range get the desired results.
Read More: How to Convert Seconds to Hours and Minutes in Excel
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.
- 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.
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.
Read More: How to Convert Minutes to Seconds in Excel
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
- Output → 2
- 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/86400 → 01446
- Output → 20:50
- Subsequently, press ENTER.
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.
- Output → 0
- 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-0 → 0.3472
- 3472*60 → 20.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-20 → 0.833
- 833*60 → 50
- Lastly, use the Ampersand (&) operator to combine the hours, minutes, and seconds.
- Lastly, press the ENTER button.
Read More: How to Convert Minutes to Tenths of an Hour in Excel
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.
Read More: How to Convert Milliseconds to Seconds in Excel
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.
- 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
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.
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.
You may download the following Excel workbook for better understanding and practice yourself.
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..
Related Articles
- How to Convert Minutes to Days in Excel
- How to Convert Minutes to Hundredths in Excel
- How to Convert Seconds to Hours Minutes Seconds in Excel
- How to Convert Minutes to Decimal in Excel
<< Go Back to Convert Time to Hours | Time Conversion | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I have an other convert method for you to convert Seconds to hh mm ss in Excel.
Suppose the seconds value is in field F3.
=CONCATENATE((IF((LEN((TRUNC((F3/3600);0)))=1);”0″;””));(TRUNC((F3/3600);0));”:”;(IF((LEN((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0)))=1);”0″;””));(TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0));”:”;(IF((LEN((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))-((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0))*60));0)))=1);”0″;””));(TRUNC(((F3-((TRUNC((F3/3600);0))*3600))-((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0))*60));0)))
Regards,
Adriaan Meilink
Thanks for your suggestions.