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

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73
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!
 

Some videos you may like

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
Joined
Jan 27, 2010
Messages
824
Office Version
  1. 365
Platform
  1. Windows
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"))
 
Solution

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73
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
Joined
Jan 27, 2010
Messages
824
Office Version
  1. 365
Platform
  1. Windows
The singular/plural thing could be addressed but it would pretty much double the length of the formula.
 

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73

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?
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
824
Office Version
  1. 365
Platform
  1. Windows
Glad you were able to sort it. I would be appreciative if you could show your final formula.
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top