How to Convert Seconds to Hours Minutes Seconds in Excel

Since Excel stores time in a numeric system, it is a simple process to convert seconds into hours, minutes, and seconds format. In this article we will demonstrate 5 quick ways. We’ll use the dataset below, and convert the Time Passed values into hours, minutes, and seconds format.

convert seconds to hours minutes seconds excel

We used Microsoft Excel 365 version to perform all the operations in this article.

Method 1 – Using INT and ROUND Functions

Steps:

In cell E5, copy and paste the following formula:

=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)

Cell D5 refers to the Time Passed in seconds.

Formula Breakdown:

INT(D5/3600) → rounds a number to the nearest integer. Here, cell D5 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) → 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. 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, we use the Ampersand (&) operator to combine the hours, minutes, and seconds.

Use the Fill Handle tool to copy the formula into the cells below.

The results should look like the image below.

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 is what you’re looking for!

Steps:

In cell E5, enter this simple expression:

=D5/(60*60*24)

In this formula, cell D5 indicates the Time Passed in seconds. The denominator 60*60*24 is the number of seconds in 1 day.

Press CTRL + 1 to open the Format Cells dialog box.
Select the Custom tab.
From the list, choose the h:mm:ss option.
Click the OK button.

Copy the formula and the formatting to the cells below using the Fill Handle.

The output should look like the picture below.

Method 3 – Using CONVERT Function

The CONVERT function converts a value from one unit to another unit.

Steps:

Go to cell E5 and enter 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 5235 sec to days.

Press the CTRL + 1 keys to open the Format Cells dialog box.
Select the Custom tab >> choose the h:mm:ss option.
Click the OK button.

The result should look like the screenshot below.

Method 4 – Using TEXT and INT Functions

Steps:

In cell E5, enter the formula below:

=TEXT((D5/86400)-INT(D5/86400),"h:mm:ss")

In this expression, (D5/86400)-INT(D5/86400) is the value argument while “h:mm:ss” represents the format_text argument. The TEXT function converts the time in seconds in cell D5 to time in hours, minutes, and seconds.

The output should look like picture below.

Method 5 – Using TEXT, CHOOSE and MATCH Functions

Here, we’ll apply the TEXT, CHOOSE and MATCH functions to convert the time in seconds to the h:mm::ss format.

Steps:

In cell E5, enter the following formula:

=TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))

In this formula, cell D5 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. {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 based on an index number. Here, 3 is the index_num argument while “s “”sec”””, “m “”min”” s “”sec”””, “[h] “”hrs”” m “”min”” s “”sec””” represent the value1, value2, and value3 arguments. As result of the 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