How to Convert Seconds to Hours Minutes Seconds in Excel

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.

convert seconds to hours minutes seconds excel

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.

Using INT and ROUND Functions

Using Fill Handle

Finally, the results should look like the image given below.

Using INT and ROUND Functions

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.

Using Format Cells Option

  • 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.

Custom Time Format

  • Lastly, copy the formula and the formatting to the cells below and your output should look like the picture shown below.

Using Format Cells Option

Read More: How to Convert Seconds to Minutes in Excel


Similar Readings


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.

Utilizing CONVERT Function

  • 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.

Using Custom Time Format

Finally, your result should look like the screenshot given below.

Utilizing CONVERT Function

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.

Applying TEXT and INT Functions

Eventually, your output should appear as shown in the picture below.

Using Fill Handle

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

Employing TEXT, CHOOSE and MATCH Functions

Subsequently, your output should look like the screenshot shown below.

convert seconds to hours minutes seconds excel

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.

Practice Section


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!


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo