While working in Microsoft Excel, we store time for multiple tasks. Sometimes we need to convert those times into hours, minutes, seconds, and days to get the perfect data. Today in this article, I am sharing how to convert seconds to hours and minutes in Excel. Stay tuned!
In the following, I have shared 4 simple and quick steps to convert seconds to hours and minutes in Excel.
Suppose we have a dataset of some “Work Names” and their “Completing Time” in “Seconds”. Now using multiple methods we are going to convert these seconds into hours and minutes.
1. Dividing with a Numeric Value to Convert Seconds to Hours and Minutes in Excel
If you are looking for a simple solution to convert seconds to hours and minutes then you are at the right place. Here in this method, I will explain a simple method just by dividing the second value by a numeric value.
Steps:
- First, choose a cell to apply the formula. Here I have selected cell (D5).
- Second, apply the following formula-
=C5/(60*60*24)
- Then press Enter to get the output.
- Now, pull the “fill handle” down to fill all the cells.
- As you can see, the output values are decimal values. So, we will change the format by selecting all the output and pressing Ctrl+1.
- After that, a new window named “Format Cells” will appear.
- From the new window, select “Custom” and then choose “h:mm” from the “Type” option.
- Hence, press OK to continue.
- Finally, we have converted the seconds’ value to hours and minutes using a simple formula. Simple isn’t it?
Read More: How to Convert Seconds to Minutes in Excel
2. Applying Excel CONVERT Function to Convert Seconds to Hours and Minutes
You can also use the CONVERT function to convert seconds to hours and minutes in Excel. Using the CONVERT function you can convert one measurement to another. Follow the steps below-
Steps:
- Above all, choose a cell (D5) to apply the following formula-
=CONVERT(C5,"sec","day")
- Hence, hit the Enter button and drag down the “fill handle” to fill.
- Now selecting the outputs press Ctrl+1 to change the format.
- Then, from the new popped-up dialog box choose “h:mm” from the “Custom” option and press OK.
- In summary, we have successfully converted seconds to hours and minutes within seconds.
Read More: How to Convert Minutes to Seconds in Excel
3. Combining TEXT and INT Functions
Sometimes it seems boring to apply formulas and change the format to convert. No worries! This time I will show you a quick trick by which you just need to use the formula and the final output will be in your hands.
Steps:
- Similarly, select a cell (D5) and write the formula down-
=TEXT((C5/86400)-INT(C5/86400),"h:mm")
- (C6/86400)-INT(C6/86400) in this part of the formula is a value argument where the INT function changes decimal values to integer values.
- The TEXT function converts the time to the format “h:mm” in this argument.
Read More: How to Convert Minutes to Hours and Minutes in Excel
4. Use of INT Function to Convert Seconds to Hours, Minutes in Excel
Well, you can also get your precious output using the INT function in Excel. To do so-
Steps:
- In the same fashion, choose a cell and put the below formula down-
=INT(C5/3600)&":"&INT(((C5/3600)-INT(C5/3600))*60)
- =INT(((C5/3600)-INT(C5/3600))*60) in this part of the formula we collect the “minute” value.
- Where, =INT(((C5/3600) in this section we divide our cell value with “3600” as 3600 seconds is one hour and the INT function converts the decimal output “1.229” into integer values which is “1”.
- Then we subtract the previous =INT(((C5/3600) value with “INT(C5/3600)” and multiply by “60” as 60 mins equal to 1 hour showing an output of “13”.
Read More: How to Convert Milliseconds to Seconds in Excel
How to Apply Formula to Convert Seconds to Hours, Minutes, and Seconds in Excel
In the previous methods, I have described all the short techniques to convert seconds to hours, minutes, and seconds. Well, you might need to convert seconds to hours, minutes, and seconds. Well, I have a quick solution for this with a single formula. Here we will combine the TEXT, MATCH, and CHOOSE functions to convert values. To do that, please follow the steps below:
Steps:
- Starting with choosing a cell (D5) to apply the formula:
=TEXT(C5/86400,CHOOSE(MATCH(C5,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))
- MATCH(C5,{0,60,3600},1) → it extracts the position in an array matching the total value. C5 is the lookup_value and {0,60,3600} is the lookup_array argument from where it is matched. “1” is the match_type argument indicating the less than criteria.
- The output is “3”.
- CHOOSE(3,”s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec”””)) → in this part “3” is the index_num while “s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec””” represents the value1, value2, value3 inside the string.
- Confirming output → [h] “hrs” m “min” s “sec”
- TEXT(C5/86400,”[h] “”hrs”” m “”min”” s “”sec”””) → here it will convert a text value into number format.
- The output from “C5/86400” will be shown to “[h] “”hrs”” m “”min”” s “”sec””” this format.
- The output stands to “0.0512”
- Our final output is “ 1 hrs 13 mins 45 sec”.
Read More: Excel Convert Seconds to hh mm ss
Things to Remember
- You can also go to the “Format Cells” feature from the “Number Format” option in the home ribbon.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover all the methods to convert seconds to hours and minutes in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.
Related Articles
- How to Convert Minutes to Days in Excel
- How to Convert Minutes to Tenths of an Hour in Excel
- How to Convert Minutes to Hundredths 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!
