Display Sum of hours as an equivalent in days and hours

[Yipeeee]

Hi,
In XL 2003
I'm trying to do a custom format on a sum of hours.
I want to display the sum of the hours as an equivalent in days and hours.

I have a guy working for me on a project.
He is supposed to be working 8 hours per day but
his real number of hours per day varies.


I have his daily working hours in a list.
Date Hours Hours as days
01/12/2007 02:00
02/12/2007 05:30
01/12/2007 03:30
Sum 11:00 01:22


That's OK, using the [h] in the custom format.
But I want to display the sum of the hours as an equivalent in days and
hours.

I have the hours summed in a Pivot table and that is working basically OK.
In the Pivot table I have inserted a formula that divides the hours by 8.
But the formula result is not displaying the way I want it.

I need the result as this "1 day, 3 hours"
What do I need to use as the custom format ?

Thanks in advance.

Yipeeee

 

[Bob Phillips]

Maybe try this.

First, change the SUM formula to

=INT(SUM(rng)/8*24)+MOD(SUM(rng),8/24)

and apply a custom format of

d "day(s)" hh:mm

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Yipeeee]

Thanks Bob !
Looks like that did the trick.

Yipeeeeeeeeeeee !