ExcelInsider Team
While importing data from a tracker or some other program, we often encounter that the time data is imported as seconds. Converting the seconds to “hh mm ss” format is mandatory for better readability. In this article, we will learn how to convert seconds to hh mm ss in excel.
➤ Take a separate column to get the converted data.
➤ Write this formula:
=TEXT(E2/86400, “hh:mm:ss”)
➤ Replace E2 with the source cell with seconds.
➤ Autofill other cells in the column
That was one smart way to do this. But there is another way to do that as well. Besides, you are probably seeking an explanation for the method we have just used. Keep reading to learn about the methods and the explanations for them.
Converting Second to HH MM SS Format Using TEXT Function
In this dataset, we have some project data and the allotted time for each part of the project. However, the time is recorded as seconds. We are going to convert them to hh:mm:ss in order to make them more understandable.
Using the TEXT function, we can convert the seconds to hh:mm:ss without changing the cell format. Here is how to do it:
➤ Use a column next to the table for the formatted data
➤ Enter this formula right under the heading:
=TEXT(E2/86400, “hh:mm:ss”)
➤ Autofill other cells if you need.
Applying Number Formatting
In this method, we are going to change the cell format to show the number in our chosen format. Carefully follow the steps below:
➤ Just like the previous method, take a helper column and write this formula:
=E2/86400
➤ Autofill other cells
➤ While the auto-filled cells are selected, go to the Number section in the Home From the dropdown menu, select “More Number Formats…”.
➤ From the new window, select Custom from the left panel, and write hh:mm:ss in the “Type:” box. If you find it difficult to follow through, check the image.
➤ Press OK and check the sheet for result.
Frequently Asked Questions
How to convert milliseconds to hh mm ss format in Excel?
Use this formula:
=TEXT(E2/86400000, “hh:mm:ss”)
Replace E2 with your input cell.
How do you convert HH MM SS to seconds in Excel?
The formula to do this is below:
=HOUR(F2)*3600 + MINUTE(F2)*60 + SECOND(F2)
Replace F2 with the cell that has data formatted as hh:mm:ss.
What is the formula to change seconds to hours?
Divide the seconds value by 3600 to get hours.
How to subtract hh mm ss in Excel?
Write this formula:
=TEXT (B2-A2, “hh:mm:ss”)
Replace B2 with the second time and A2 with the first time.
What is time in Excel formula?
The time function returns 24-hour time in a decimal format. The range of the output value is more than 0 and less than 1.
Wrapping Up
In this article, we have learned two ways to convert seconds to hh mm ss in excel. Download the practice workbook to learn the functions better and consider bookmarking the site for more excel tutorials. The comment section is open for you to give your feedback.
