In this tutorial, to demonstrate our methods we’ll use the following **Sudoko Competition – Completion Time** dataset, which contains **ID**, **Participant Names**, and **Completion Time (sec)** in columns **B**, **C**, and **D** respectively.

Let’s convert these times in seconds to hh mm ss format using different approaches in Excel.

We have used *Microsoft Excel 365* version here, but you may use any other version according to your convenience.

### Method 1 – Using Format Cells Option

This is the simplest way to convert seconds to hh mm ss format in Excel.

- Create a new column
**Time in hh:mm:ss Format**in**Column E**.

- Select cell
**E5**and enter the following formula:

`=D5/86400`

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

**=**

*(24 × 60 × 60)***seconds. In other words, we convert the time in seconds to a fraction of a day.**

*86400*- Press
**ENTER**.

- Press
**CTRL + 1**to open the**Format Cells**dialog box. - In the dialog box, go to the
**Number**tab. - Select
**Custom**from the**Category**list. - Enter
**hh:mm:ss**in the**Type**box. - A
**Sample**is displayed like in the image below. - Click
**OK**.

The value in cell **E5** looks like this:

- Bring the cursor to the bottom right corner of cell
**E5**. It’ll look like a plus**(+)**sign. It’s the**Fill Handle**tool. - Drag the
**Fill Handle**down to cell**E14**.

The remaining cells in the **E5:E14** range are filled with the desired results.

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

### Method 2 – Using the CONVERT Function

** Steps:**

- Select cell
**E5**and paste the following formula into the**Formula Bar**:

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

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

**“sec”**and

**“day”**point to the

**and**

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

*to_unit***the CONVERT function**converts

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

- Press
**ENTER**.

- Change the format of cell
**E5**like in**Method 1**. - Double-click on the
**Fill Handle**tool.

The remaining cells are filled with the relevant results.

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

### Method 3 – Using the TEXT Function

** Steps:**

- Go to cell
**E5**and enter 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. The**

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

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

- Press
**ENTER**.

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

### Method 4 – Using CHOOSE, MATCH, TEXT Functions

** Steps:**

- 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 thethat refers to the*lookup_value**Completion Time*.**{0,60,3600}**represents thefrom where the value is matched.*lookup_array***1**is the optionalwhich indicates*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 based on an index number. Here,**2**is thewhile*index_num***“:ss”,”m:ss”,”[h]:mm:ss”**represents,*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 thewhile*value***“m:ss”**represents the. The function converts the value of*format_text***0.01446**to**h:mm:ss**format.**1250/86400**→**01446****Output**→**20:50**

- Press
**ENTER**.

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

### Method 5 – Using INT and ROUND Functions

** Steps:**

- Select cell
**E5**and paste the following formula in it:

`=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)**→ We obtain the minutes part by subtracting**INT(D5/3600)**from**D5/3600**and multiplying the answer by**60**since there are**60**minutes in**1**hour. 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 to minutes.**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, we use the
**Ampersand (&)**operator to combine the hours, minutes, and seconds.

- Press
**ENTER**.

**Read More: **How to Convert Minutes to Tenths of an Hour in Excel

### Method 6 – Using MOD, TEXT, and TRUNC Functions

** Steps:**

- 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.

- Press
**ENTER**.

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

### Method 7. Using VBA Code

You might be wondering, is there a way to automate this task?

** Steps:**

- Construct a new column
**Time in hh:mm:ss Format**in**Column E**like in**Method 1**. - Go to the
**Developer**tab. - Select
**Visual Basic**on the**Code**group. - Alternatively, press
**ALT + F11**to perform the same task.

The **Microsoft Visual Basic for Applications** window opens.

- Go to the
**Insert**tab. - Select
**Module**from the options.

The **Code Module** opens.

- Enter 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**

- We create the macro and give it the name
**convert_sec**. - We define 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**

- We insert a For loop for the value of
**5**to**14**for**x**. - We assigned the value of cell
**D5**to the**secs**variable. - We divide the variable
**secs**by**86400**and assign the value to**converted_time**variable. - We change the format of cell
**D5**to**hh:mm:ss**format. - We place the above variable in cell
**E5**. - We move to the lower cell
**D6**and continue the above loop down to cell**D14**.

- Click the
**Run**icon or press**F5**on the keyboard to run the code.

- Return to the
**VBA**worksheet.

The **Time in hh:mm:ss Format** column is filled automatically with the correct results.

## Related Articles

- How to Convert Minutes to Days in Excel
- How to Convert Minutes to Hundredths in Excel
- How to Convert Seconds to Hours Minutes Seconds 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**

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.