Change cell format to Time format corresponding to: 37:30:55 -
Delete the multiplier - 1.16667 is 28 hours in general format.
Have fun!

"rr94527" wrote:

Hello BoniM,

Thank you for responding to my challenge. I tried your formula and got
1.16667 as an answer? I thought that might be number of days. So I
mulitplied by 24 to get the number of hours. And it came out -21.833?



"BoniM" wrote:

You basically want to take the total hours and subtract 12 hours for each
night that passes. This formula assumes that your start date is in A2 and
end date in B2, modify as needed:
=(B2-A2)-(YEARFRAC(A2,B2,3)*365/2)
The first part gives total hours down, the second part figures whole days
only (or nights) and divides by 2 to leave 12 hours per night to subtract
from the whole hours out of service.


"rr94527" wrote:

Hello,

I am trying to calculate how long a machine was down. Here is a situation.
If a machine went down at 10:00am on 3/1/07. This machine was repaired on
3/3/07 at 2:00pm. I can figure out the total time it was down which was 2
days, 4 hours (52 hours total). Obviously, a repairman is not available
24-hours, 7-days a week. He is asleep in his bed at nights. He is available
to repair the machines from 9am to 9pm 7 days a week. I want to calculate
the total time the machine was down during the repairman's schedule. In this
case, this machine was down for 28 hours (11 hours on 3/1, 12 hours on 3/2,
and 5 hours on 3/3). Is there an easier way to calcuate this for thousand
machines? Perhaps a modification of datedif function?

Please email me the replies at .
Thanks.