Eshrak Kader
In many situations, you may need to convert seconds to hours, minutes, and seconds. When you have a powerful tool like Microsoft Excel, you can perform such a task effortlessly. Since Excel stores time in a numeric system, you can easily change it to hours, minutes, and seconds format. Keeping this in mind, this article shows 5 quick ways how to convert seconds to hours minutes seconds in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
5 Ways to Convert Seconds to Hours Minutes Seconds in Excel
Considering the dataset shown in the B4:D14 cells. Here, we have the name of the Company, the model’s name of the Processor, and the Time Passed in seconds to perform a particular task.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Method-1: Using INT and ROUND Functions
For our first method, we’ll utilize the INT and ROUND functions to convert seconds to hours minutes seconds format. So, just follow these steps.
📌 Steps:
- First of all, go to the E5 cell >> now, copy and paste this formula into the Formula Bar.
=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 Time Passed in seconds.
Formula Breakdown:
- INT(D5/3600) → rounds a number to the nearest integer. Here, the D5 cell points to the Time Passed in seconds which is divided by 3600 since there are 3600 seconds in 1 hour. Thus, we get the time in hours.
- Output → 1
- 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 60 minutes in 1 hour. The INT function returns only the integer part of the answer.
- 1.454-1 → 0.454
- 0.454*60 → 27
- 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.
- 27.25-27 → 0.25
- 0.25*60 → 15
- Lastly, use the Ampersand (&) operator to combine the hours, minutes, and seconds.
- Then, use the Fill Handle Tool to copy the formula into the cells below.
Finally, the results should look like the image given below.
Read More: Convert Seconds to Hours and Minutes in Excel (4 Easy Methods)
Method-2: Using Format Cells Option
If writing complex expressions aren’t your cup of tea and you’re hoping for a simpler approach, then the following method answers your prayer! Now, allow me to demonstrate the process in the steps below.
📌 Steps:
- First and foremost, move to the E5 cell and enter this simple expression.
=D5/(60*60*24)
In this formula, the D5 cell indicates the Time Passed in seconds. Moreover, the denominator 60*60*24 is the number of seconds in 1 day.
- Next, press the CTRL + 1 keys on the keyboard to open the Format Cells dialog box.
- Now, select the Custom tab >> from the list, choose the h:mm:ss option >> click the OK button.
- Lastly, copy the formula and the formatting to the cells below and your output should look like the picture shown below.
Read More: How to Convert Seconds to Minutes in Excel
Similar Readings
- How to Convert Minutes to Hundredths in Excel (3 Easy Ways)
- Convert Minutes to Days in Excel (3 Easy Methods)
- How to Convert Hours to Percentage in Excel (3 Easy Methods)
Method-3: Utilizing CONVERT Function
Another way to convert seconds to hours minutes seconds in Excel involves using the CONVERT function which converts a value from one unit to another unit. So, let’s see it in action.
📌 Steps:
- Initially, navigate to the E5 cell and type in the following expression.
=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 5235 sec to days.
- In turn, hit the CTRL + 1 keys to open the Format Cells dialog box.
- Following this, select the Custom tab >> choose the h:mm:ss option >> click the OK button.
Finally, your result should look like the screenshot given below.
Read More: How to Convert Milliseconds to Seconds in Excel (2 Quick Ways)
Method-4: Applying TEXT and INT Functions
If you’re one of those people who enjoy using Excel formulas, then our next method has you covered. Here, we’ll combine the TEXT and INT functions to convert seconds to hours minutes seconds format. So, let’s dive in!
📌 Steps:
- Firstly, jump to the E5 cell >> in the Formula Bar, and enter the formula given below.
=TEXT((D5/86400)-INT(D5/86400),"h:mm:ss")
In this expression, the (D5/86400)-INT(D5/86400) is the value argument while the “h:mm:ss” represents the format_text argument. Here, the TEXT function converts the time in seconds in the D5 cell to time in hours, minutes, and seconds.
Eventually, your output should appear as shown in the picture below.
Read More: Convert Time to Text in Excel (3 Effective Methods)
Method-5: Employing TEXT, CHOOSE and MATCH Functions
For those of you who want to learn about more techniques, there is another way to convert seconds to hours minutes seconds in Excel. In this method, we’ll apply the TEXT, CHOOSE and MATCH functions to convert the time in seconds to the h:mm::ss format.
📌 Steps:
- Firstly, proceed to the E5 cell >> next, copy and paste this formula.
=TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))
In the above formula, the D5 cell refers to the Time Passed in seconds.
Formula Breakdown:
- MATCH(D5,{0,60,3600},1) → 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 Time Passed. 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 criteria.
- Output → 3
- CHOOSE(MATCH(D5,{0,60,3600},1),”s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec”””) → becomes
- CHOOSE(3,”s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec”””) → chooses a value or action to perform from a list of values,base on an index number. Here, 3 is the index_num argument while “s “”sec”””, “m “”min”” s “”sec”””, “[h] “”hrs”” m “”min”” s “”sec””” represents the value1, value2, and value3 arguments. According to index number 3, the function chooses the “[h] “”hrs”” m “”min”” s “”sec””” format.
- Output → [h] “hrs” m “min” s “sec”
- TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),”s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec”””)) → becomes
- TEXT(D5/86400,”[h] “”hrs”” m “”min”” s “”sec”””)) → converts a value to text in a specific number format. Here, D5/86400 is the value argument while “[h] “”hrs”” m “”min”” s “”sec””” represents the format_text argument. The function converts the value of 0.0606 to h:mm:ss format.
- 5235/86400 → 0.0606
- Output → 1 hrs 27 min 15 sec
Subsequently, your output should look like the screenshot shown below.
Read More: How to Convert Minutes to Seconds in Excel (2 Quick Ways)
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
This article describes how to convert seconds to hours minutes seconds in Excel in a quick and easy way. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are happy to answer your queries. Keep learning and keep growing!
