# 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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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

ADVERTISEMENT

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
122
Replies
3
Views
215
Replies
5
Views
189
Replies
4
Views
265
Replies
14
Views
395

Threads
1,127,898
Messages
5,627,530
Members
416,250
Latest member
darius_rebelo

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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