Have you ever thought about how to **convert seconds to hh mm ss format in Excel**? Tired of searching for it online? But can’t find the right answer anywhere? If the answer to all the above questions is yes, then you’ve come to the right place. Here, we will take you through **7** easy and convenient methods to convert seconds to hh mm ss format in Excel. Hopefully, there will be no need to go anywhere else after that.

**Table of Contents**hide

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

## 7 Methods to Convert Seconds to hh mm ss in Excel

For clarification, let’s say we have a **Sudoko Competition – Completion Time** file in our hands. This dataset contains **ID**, **Participant Names**, and **Completion Time (sec)** in columns **B**, **C**, and **D** respectively.

Now, we’ll convert these times in seconds to hh mm ss format using different approaches in Excel.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

### 1. Using Format Cells Option to Convert Seconds to hh mm ss in Excel

In our first method, we’ll show the simplest way to convert seconds to hh mm ss format in Excel. So, let’s begin.

- At first, create a new column
**Time in hh:mm:ss Format**in**Column E**.

- Secondly, select cell
**E5**and write down the following formula.

`=D5/86400`

Here, **D5** represents the *Completion Time (sec)* of the first competitor *Robin*. We divided the value of cell **D5** by **86400**. Because, ** 1** day =

**=**

*(24 × 60 × 60)***seconds. Basically, we converted the time in seconds to a fraction of a day.**

*86400*- Then, press
**ENTER**.

- At this moment, press
**CTRL + 1**to open the**Format Cells**dialog box. - In the dialog box, go to the
**Number**tab. - Then, select
**Custom**from the**Category**list. - After that, write down
**hh:mm:ss**in the**Type**box. - Here, we can see the
**Sample**like in the image below. - Later, click
**OK**.

- Now, the value in cell
**E5**looks like that.

- At this point, bring the cursor to the bottom right corner of cell
**E5**. Thus, it’ll look like a plus**(+)**sign. It’s the**Fill Handle**tool. - Now, drag the
**Fill Handle**up to cell**E14**.

- Thus, the remaining cells in the
**E5:E14**range get the desired results.

**Read More: ****Convert Seconds to Hours and Minutes in Excel (4 Easy Methods)**

### 2. Inserting CONVERT Function to Convert Seconds to hh mm ss in Excel

If you’re one of those people who enjoy using Excel formulas, then our next methods have you covered. Just follow these simple steps.

**📌 Steps:**

- At the very beginning, select cell
**E5**and paste the following formula into the**Formula Bar**.

`=CONVERT(D5,"sec","day")`

In the above formula, **D5** represents the ** number** argument, and the

**“sec”**and

**“day”**point to the

**and**

*from_unit***arguments respectively. Here, the**

*to_unit***CONVERT function**converts the

**1250**seconds to a fraction of days.

- After that, tap the
**ENTER**key.

- Presently, change the format of cell
**E5**like**Method 1**. - Then, double-click on the
**Fill Handle**tool.

- Hence, the remaining cells get filled by the relevant results.

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

### 3. Applying TEXT Function to Convert Seconds to hh mm ss in Excel

If you are curious to learn about the **TEXT function** in Excel, this method may come in handy. It’s simple and easy, just follow along.

**📌 Steps:**

- Firstly, go to cell
**E5**and put the following formula into the cell.

`=TEXT(D5/86400,"hh:mm:ss")`

In this expression, the **D5/86400** is the ** value** argument while the

**“hh:mm:ss”**represents the

**argument. Here, the**

*format_text***TEXT function**converts the time in seconds in the

**D5**cell to time in hh:mm:ss format.

- Secondly, hit the
**ENTER**key.

**Read More: ****How to Convert Minutes to Seconds in Excel (2 Quick Ways)**

### 4. Utilizing CHOOSE, MATCH, TEXT Functions

For those of you who want to learn about more techniques, there is another way to convert seconds to hh mm ss format in Excel. In this method, we’ll apply the **TEXT**, **CHOOSE** and **MATCH** functions to convert the time in seconds to the desired format. Let’s see the process in detail.

**📌 Steps:**

- Initially, select cell
**E5**and insert the formula below.

`=TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),":ss","m:ss","[h]:mm:ss"))`

In the above formula, the **D5** cell refers to the *Completion Time (sec)*.

**Formula Breakdown**

**MATCH(D5,{0,60,3600},1)**→ The**MATCH function**returns the relative position of an item in an array matching the given value. Here,**D5**is theargument that refers to the*lookup_value**Completion Time*. Following,**{0,60,3600}**represents theargument from where the value is matched. Lastly,*lookup_array***1**is the optionalargument which indicates the*match_type***Less than****Output**→**2**

**CHOOSE(MATCH(D5,{0,60,3600},1),”:ss”,”m:ss”,”[h]:mm:ss”)**→ becomes**CHOOSE(2,”:ss”,”m:ss”,”[h]:mm:ss”)**→ The**CHOOSE function**chooses a value or action to perform from a list of values,base on an index number. Here,**2**is theargument while*index_num***“:ss”,”m:ss”,”[h]:mm:ss”**represents the,*value1*, and*value2*According to index number*value3***2**, the function chooses the**“m:ss”**format.**Output**→**“m:ss”**

**TEXT(D5/86400,CHOOSE(MATCH(D5,{0,60,3600},1),”:ss”,”m:ss”,”[h]:mm:ss”))**→ becomes**TEXT(D5/86400,”m:ss”)**→ converts a value to text in a specific number format. Here,**D5/86400**is theargument while*value***“m:ss”**represents theThe function converts the value of*format_text***0.01446**to**h:mm:ss**format.**1250/86400**→**01446****Output**→**20:50**

- Subsequently, press
**ENTER**.

**Read More: ****How to Convert Seconds to Minutes in Excel**

### 5. Employing INT and ROUND Functions

As with Excel, there are multiple ways of doing the same task. Hence, you can perform the solution in another way. Let’s explore the method step by step.

**📌 Steps:**

- Initially, select cell
**E5**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)`

Here, the **D5** cell refers to the *Completion Time* in seconds.

**Formula Breakdown**

**INT(D5/3600)**→ The**INT function**rounds a number to the nearest integer. Here, the**D5**cell points to the*Completion Time*in seconds which is divided by**3600**since there are**3600**seconds in**1**Thus, we get the time in hours.**Output**→**0**

**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 are**60**minutes in**1**The**INT function**returns only the integer part of the answer.**3472-0**→**0.3472****3472*60**→**20.833****Output**→**20**

**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.**833-20**→**0.833****833*60**→**50**

- Lastly, use the
**Ampersand (&)**operator to combine the hours, minutes, and seconds.

- Lastly, press the
**ENTER**button.

**Read More: ****How to Convert Minutes to Tenths of an Hour in Excel (6 Ways)**

### 6. Implementing MOD, TEXT, and TRUNC Functions

In this method, we’ll combine some functions to perform the task. So, without further delay, let’s dive in!

**📌 Steps:**

- Primarily, jump to cell
**E5**and paste the following formula into the cell.

`=TRUNC(D5/3600)&TEXT(MOD(D5/86400,1),":mm:ss")`

Here, the **MOD function** takes **D5/86400** as the ** number** argument and

**1**as the

**argument. It returns**

*divisor***0.01446**which is the

**argument of the**

*value***TEXT function**. The

**TEXT function**converts the format of the text as

**“:mm:ss”**which is the

**argument of the above function.**

*format_text***TRUNC(D5/3600)**gives

**0**as result. Because the

**TRUNC function**returns the integer part of a number.

- Correspondingly, hit
**ENTER**.

**Read More: ****How to Convert Milliseconds to Seconds in Excel (2 Quick Ways)**

### 7. Engaging VBA Code

You might be wondering, is there a way to automate this task? Then **VBA** has you covered. Just follow along.

**📌 Steps:**

- At first, construct a new column
**Time in hh:mm:ss Format**under**Column E**like**Method 1**. - Secondarily, go to the
**Developer**tab. - Then, select
**Visual Basic**on the**Code**group. - Alternatively, press
**ALT + F11**to do the same task.

- Instantly, the
**Microsoft Visual Basic for Applications**window opens. - Afterward, move to the
**Insert**tab. - Later, select
**Module**from the options.

- Immediately, it opens the
**Code Module**. - Then, write down the following code in the
**Module**.

```
Sub convert_sec()
Dim secs As Integer, converted_time As Date
For x = 5 To 14
secs = Cells(x, 4).Value
converted_time = secs / 86400
Cells(x, 5).NumberFormat = "hh:mm:ss"
Cells(x, 5).Value = converted_time
Next x
End Sub
```

**Code Breakdown**

**Sub convert_sec()**

**Dim secs As Integer, converted_time As Date**

- Firstly, we create the macro and give it the name
**convert_sec**. - Then, we defined the two variables.

**For x = 5 To 14**

**secs = Cells(x, 4).Value**

**converted_time = secs / 86400**

**Cells(x, 5).NumberFormat = “hh:mm:ss”**

**Cells(x, 5).Value = converted_time**

**Next x**

- After that, we insert a for loop for the value of
**5**to**14**for**x**. - Later, we assigned the value of cell
**D5**to the**secs**variable. - Next, divide the variable
**secs**by**86400**and assign the value to**converted_time**variable. - At this moment, change the format of cell
**D5**to**hh:mm:ss**format. - Subsequently, put the above variable to cell
**E5**. - Finally, move to the lower cell
**D6**and continue the above loop till going to cell**D14**.

- Lastly, select the
**Run**icon or press**F5**on the keyboard.

- Now, return to the
**VBA**worksheet. - Thus, the
**Time in hh:mm:ss Format**column gets filled automatically with the correct results.

**Read More: ****Convert Decimal Coordinates to Degrees Minutes Seconds in Excel**

## Practice Section

For doing practice by yourself we have provided a **Practice** section like below in each sheet on the right side. Please do it by yourself.

## Conclusion

Thank you for reading this article. I hope all of the methods mentioned above to convert seconds to hh mm ss format in Excel will now prompt you to apply them in your Excel spreadsheets more effectively. Don’t forget to download the **Practice** file. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this **website**.

I have an other convert method for you to convert Seconds to hh mm ss in Excel.

Suppose the seconds value is in field F3.

=CONCATENATE((IF((LEN((TRUNC((F3/3600);0)))=1);”0″;””));(TRUNC((F3/3600);0));”:”;(IF((LEN((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0)))=1);”0″;””));(TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0));”:”;(IF((LEN((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))-((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0))*60));0)))=1);”0″;””));(TRUNC(((F3-((TRUNC((F3/3600);0))*3600))-((TRUNC(((F3-((TRUNC((F3/3600);0))*3600))/60);0))*60));0)))

Regards,

Adriaan Meilink

Thanks for your suggestions.