To convert seconds into hours or minutes in Excel, you need to divide the seconds value that you have by the total number of seconds that you have in one single day. After that, you need to change the format to hours and minutes.
In this tutorial, you will learn to write and understand these formulas in detail. For your reference, in the below example, you have 10000 seconds in cell A1.
Convert Seconds into Hours (hh:mm:ss)
Below are the steps to write this formula:
- First, in a cell enter the formula to divide seconds by 86400 (the total number of seconds in a day).
- After that, open the format options (Ctrl + 1).
- Now, from the list of categories, click on “Custom”.
- Next, in the type input bar, enter [hh]:mm or [hh]:mm:ss.
- In the end, click OK to apply the formatting.
When you hit OK, it gives you the total number of hours and minutes.
Instead of using the hh:mm:ss, we are here using [hh]:mm or [hh]:mm:ss.
Because when you use hh:mm and you have seconds that are more than the total number of seconds in a day, you will get the wrong result.
If you have 27 hours, it will show you 3 hours by passing the 24 hours daily. That’s why you should use [hh]:mm:ss and [hh]:mm.
Convert Seconds into Minutes
In the same way, you can convert seconds into minutes, you need to use the same steps, but you need to use a different custom format. Once you enter the formula in the cell, change the format to [mm]:ss and click OK.
Using Text Function to Convert Seconds into Hours and Minutes
Yes, you can also use the TEXT function. This allows the conversion of seconds into the desired result without changing the format of the cell. In the TEXT function, you can specify the format inside the function.
In the first argument of TEXT, you need to enter the same formula seconds/total number of seconds in a day. After that, in the second argument (format_text), enter the format you want to give.
=TEXT(A1/86400,"[hh]:mm")
=TEXT(A1/86400,"[mm]:ss")