In this tutorial, to demonstrate our methods we’ll use the following Sudoko Competition – Completion Time dataset, which contains ID, Participant Names, and Completion Time (sec) in columns B, C, and D respectively.
Let’s convert these times in seconds to hh mm ss format using different approaches in Excel.
We have used Microsoft Excel 365 version here, but you may use any other version according to your convenience.
Method 1 – Using Format Cells Option
This is the simplest way to convert seconds to hh mm ss format in Excel.
- Create a new column Time in hh:mm:ss Format in Column E.
- Select cell E5 and enter the following formula:
=D5/86400
Here, D5 represents the Completion Time (sec) of the first competitor Robin. We divide the value of cell D5 by 86400, because 1 day = (24 × 60 × 60) = 86400 seconds. In other words, we convert the time in seconds to a fraction of a day.
- Press ENTER.
- Press CTRL + 1 to open the Format Cells dialog box.
- In the dialog box, go to the Number tab.
- Select Custom from the Category list.
- Enter hh:mm:ss in the Type box.
- A Sample is displayed like in the image below.
- Click OK.
The value in cell E5 looks like this:
- Bring the cursor to the bottom right corner of cell E5. It’ll look like a plus (+) sign. It’s the Fill Handle tool.
- Drag the Fill Handle down to cell E14.
The remaining cells in the E5:E14 range are filled with the desired results.
Read More: How to Convert Seconds to Hours and Minutes in Excel
Method 2 – Using the CONVERT Function
Steps:
- Select cell E5 and paste the following formula into the Formula Bar:
=CONVERT(D5,"sec","day")
In this 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 1250 seconds to a fraction of days.
- Press ENTER.
- Change the format of cell E5 like in Method 1.
- Double-click on the Fill Handle tool.
The remaining cells are filled with the relevant results.
Read More: How to Convert Minutes to Hours and Minutes in Excel
Method 3 – Using the TEXT Function
Steps:
- Go to cell E5 and enter 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. The TEXT function converts the time in seconds in the D5 cell to time in hh:mm:ss format.
- Press ENTER.
Read More: How to Convert Minutes to Seconds in Excel
Method 4 – Using CHOOSE, MATCH, TEXT Functions
Steps:
- 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 that refers to the Completion Time. {0,60,3600} represents the lookup_array from where the value is matched. 1 is the optional match_type which indicates 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 based on an index number. Here, 2 is the index_num while “:ss”,”m:ss”,”[h]:mm:ss” represents 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 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
- Press ENTER.
Read More: How to Convert Seconds to Minutes in Excel
Method 5 – Using INT and ROUND Functions
Steps:
- Select cell E5 and paste the following formula in it:
=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) → We obtain the minutes part by subtracting INT(D5/3600) from D5/3600 and multiplying the answer by 60 since there are 60 minutes in 1 hour. 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 to minutes. 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, we use the Ampersand (&) operator to combine the hours, minutes, and seconds.
- Press ENTER.
Read More: How to Convert Minutes to Tenths of an Hour in Excel
Method 6 – Using MOD, TEXT, and TRUNC Functions
Steps:
- 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.
- Press ENTER.
Read More: How to Convert Milliseconds to Seconds in Excel
Method 7. Using VBA Code
You might be wondering, is there a way to automate this task?
Steps:
- Construct a new column Time in hh:mm:ss Format in Column E like in Method 1.
- Go to the Developer tab.
- Select Visual Basic on the Code group.
- Alternatively, press ALT + F11 to perform the same task.
The Microsoft Visual Basic for Applications window opens.
- Go to the Insert tab.
- Select Module from the options.
The Code Module opens.
- Enter 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
- We create the macro and give it the name convert_sec.
- We define 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
- We insert a For loop for the value of 5 to 14 for x.
- We assigned the value of cell D5 to the secs variable.
- We divide the variable secs by 86400 and assign the value to converted_time variable.
- We change the format of cell D5 to hh:mm:ss format.
- We place the above variable in cell E5.
- We move to the lower cell D6 and continue the above loop down to cell D14.
- Click the Run icon or press F5 on the keyboard to run the code.
- Return to the VBA worksheet.
The Time in hh:mm:ss Format column is filled automatically with the correct results.
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.