How to Convert Seconds to Hours Minutes Seconds in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Convert Seconds to Hours Minutes Seconds in Excel: 5 Quick Ways

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

  • Then, use the Fill Handle tool to copy the formula into the cells below.

Using Fill Handle

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

Using INT and ROUND Functions

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


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


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


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


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


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


Download Practice Workbook

You can download the practice workbook from the link below.


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.


Related Articles


<< Go Back to Convert Time to Hours | Time Conversion | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo