Excel 2007 : Absolute Days in Display

[amitgogna]

Hi,

I too am stuck with a peculiar issue.

I have a sheet with seconds (from 0 to 10,000) against each person stating how much they used the phone on each day, I have converted the seconds to time by using <<=C2/86400>>, where c2 is the cell (column) for seconds. I wanted to display this as <<xx Days, xx Hours, xx Minutes, xx Seconds>>. So I tried using the custom formatting of the cell by putting <<dd "Days", hh "Hours", mm "Minutes", ss "Seconds";@>>. Worked fine till the usage goes over 30 days. If the person uses the phone for (lets say) 35 days, 2 hours and 31 minutes, it displays <<05 Days, 02 Hours, 31 Minutes, 00 Seconds>>. Is there any setting to get the absolute number of days stated as in <<35 days, 02 hours and 31 minutes, 00 Seconds>>?

Note: All workings have to dome in the same cell as the data is subjected to counts and sums using pivots.

Attached image should clear the requirements. Requesting Help.

Issue.jpg

Thanks.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Please don't upload pictures. None of us is inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.

Regards

[amitgogna]

Sorry about that. Excitement and anticipation; not to mention the issue I am stuck in.

The file is uploaded and attached. Please see the pivot (and its data). 27 days, 4 Days and 2 days add up to 4 days. Although the month is not there and is not required. The total needs to be absolute days with hour, minutes and seconds.

I would be glad to answer any query you have regarding the data, if required

Thanks.

[Richard Buttrey]

Hi,

It seems tricky to incorporate in the pivot table since Excel doesn't seem to like putting the [d] in square brackets like you can with [h] or [m] and which keep the total.

However if you can live with a cell outside the pivot table then

=INT(B8)&" Days "&INT(MOD(B8,INT(B8))*24)&" Hours and "&MINUTE(B8)&"Minutes"

where B8 is currently the Grand Total cell for the Time Duration. Thinking about this though and since the PT can vary in length it's probably best to change the B8 reference and use instead =SUM(Data!M:M). The only downside I suppose is this won;t be true if you restrict your PT to less than the complete analysis.

If I can think of anything more appropriate I'll come back.

Regards

[amitgogna]

Thanks, It worked. I used:

=INT(B4)&" Days, "&INT(MOD(B4,IF(INT(B4)=0,1,INT(B4)))*24)&" Hours, "&MINUTE(B4)&" Minutes, and "&SECOND(B4)&" Seconds"

I will use the above for now but still request and appreciate if you could help (as you said) in getting something more appropriate.