Excel 2007 : Absolute Days in Display

**amitgogna** · 03-13-2012, 10:45 AM

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** · 03-13-2012, 12:47 PM

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