# Need to convert total seconds to a string like 3 Days; 19 Hours; 11 Minutes

#### jeff_cp

##### Board Regular
I have a cell that contains total number of seconds. This is cell D11 and it's value shows, for example, 328302.

In cell E11, I have the following formula to convert this to Days:Hours:Minutes
=INT(D11/86400) & ":" & TEXT((D11/86400)-INT(D11/86400),"hh:mm")

This formula results in a value of 3:19:11

In field F11, I want to show the text 3 Days; 19 Hours; 11 Minutes

If the total seconds would result in 0 days, I want to only show the hours and minutes like 19 Hours; 11 Minutes

And if the total seconds would result in 0 days and 0 hours, I want to only show the minutes like
11 Minutes

Any help with this would be greatly appreciated!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### 6StringJazzer

##### Well-known Member
Tested:

Excel Formula:
``````=IF(INT(D11/86400)>0,
INT(D11/86400) & " Days; "&HOUR(D11/86400)&" Hours;"&MINUTE(D11/86400)&" Minutes",
IF(HOUR(D11/86400)>0,
HOUR(D11/86400)&" Hours;"&MINUTE(D11/86400)&" Minutes",
MINUTE(D11/86400)&" Minutes"))``````

#### jeff_cp

##### Board Regular
Perfect! I changed it slightly so Days show as D, Hours show as H and Minutes show as M. That way, if there's only 1 hour, it doesn't show as 1 Hours.

#### 6StringJazzer

##### Well-known Member
The singular/plural thing could be addressed but it would pretty much double the length of the formula.

#### jeff_cp

##### Board Regular

I ran into a rounding issue with the formula. In this case, cell D11 shows 287 seconds and the formula is returning "4 M". If you divide 287 by 60, you get 4.78 minutes so I'd like to round the "4 M" to "5 M". Is that easy to do?

#### jeff_cp

##### Board Regular
I extended this out to include seconds so no need for the rounding.

#### 6StringJazzer

##### Well-known Member
Glad you were able to sort it. I would be appreciative if you could show your final formula.

Replies
8
Views
153
Replies
5
Views
100
Replies
3
Views
221
Replies
8
Views
115
Replies
5
Views
195

1,129,879
Messages
5,638,800
Members
417,053
Latest member
SaturdayNight

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back