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

[jeff_cp]

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!

 

[6StringJazzer]

Tested:

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

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]

The singular/plural thing could be addressed but it would pretty much double the length of the formula.

 

[jeff_cp]

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]

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

 

[6StringJazzer]

Glad you were able to sort it. I would be appreciative if you could show your final formula.