datedif challenge

[rr94527]

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 (e-mail address removed).
Thanks.

 

[BoniM]

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]

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?

 

[Pete_UK]

Assuming you have the start date in A2, start time in B2, repair date
in C2 and repair time in D2 (all in Excel date and time formats), this
formula will give you the number of working hours that the machine was
broken - format as number with 2 dp:

=IF(OR(A2=0,B2=0,C2=0,D2=0,C2<A2,B2<9/24,D2>21/24,(C2+D2)<(A2+B2)),"
",(C2-A2-1)*12+(21/24-B2)*24+(D2-9/24)*24)

The first part of the formula (up to "") is just checking for invalid
data in any of the four cells. This is all one formula - I've just
split it manually to avoid awkward line breaks.

If instead of separate fields for dates and times you have start date-
time in A1 and repair date-time in B1, this formula will calculate
lost hours as above:

=IF(OR(A2=0,B2=0,B2<A2,MOD(A2,1)<9/24,MOD(B2,1)>21/24),"
",(INT(B2)-INT(A2)-1)*12+(21/24-MOD(A2,1))*24+(MOD(B2,1)-9/24)*24)


Both formulae work by taking 12 hours for each complete day and then
adding the number of hours (up to 9:00pm, or 21:00:00) from the first
day to the number of hours (after 9:00am) of the final day, and both
formulae can be copied down a column if you have many rows of data.

Hope this helps.

Pete

 

[BoniM]

Okay, it's 3 in the morning AGAIN.. ;-) and it's not going to get any better
than this for now! For the guy that works 9am to 6pm Monday to Friday and
9am to 2pm on Saturday and has Sunday off
=IF(WEEKDAY(B2)-DATEDIF(A2,B2,"d")>0,(B2-A2)-HOUR((B2-A2)/24)*0.625,(B2-A2)-HOUR((B2-A2)/24)*0.625+IF(DATEDIF(A2,B2,"d")-WEEKDAY(B2)>=7,INT((DATEDIF(A2,B2,"d")-WEEKDAY(B2))/7)*0.5416667,0.541667))

Here's how it works. Checking to see if it was there over a weekend:
=IF(WEEKDAY(B2)-DATEDIF(A2,B2,"d")>0
if it wasn't, the weekday would be bigger than how long it was there and we
can just use regular formula, adjusted for his shorter workday:
(B2-A2)-HOUR((B2-A2)/24)*0.625
If it was there over a weekend, we use the regular formula:
(B2-A2)-HOUR((B2-A2)/24)*0.625
and then check and see if it were more than one weekend:
IF(DATEDIF(A2,B2,"d")-WEEKDAY(B2)>=7
if how long it was there is seven or more days bigger than the day it was
completed, than it was there for more than one weekend. How many weekends
more than one can be determined by dividing the number we get from above by
seven and rounding down:
INT((DATEDIF(A2,B2,"d")-WEEKDAY(B2))/7)*0.5416667,
if it was more than one, we add the extra. Otherwise, just a single weekend
worth of extra time off - lucky guy, do those other two know about him?
0.541667
Have fun!

 

[BoniM]

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]

Eureka!! Thanks BoniM!! You rock! And don't be sorry! I'm learning from this.

Now, here is where it becomes a little tricky. What if the repairman's
schedule was from 7am to 11pm? I have three repairmen with different
schedule. Of course each one is responsible for his own territory. I picked
the easiest one who works from 9am to 9pm 7 days a week. But the other two
repairmen have different schedule. One is from 7am to 10pm 7 days a week.
The third one works 9am to 6pm Monday to Friday and 9am to 2pm on Saturday.

I appreciate your help with this one.

 

[BoniM]

ok, 7 to 10 seven days/week is easy, just change the formula like this:
=(B2-A2)-HOUR((B2-A2)/24)*0.375
to only take nine hours out per night.
The guy that takes part of the weekend off is going to take a little more
work!

 

[BoniM]

Sorry... copied wrong one:

=(B2-A2)-HOUR((B2-A2)/24)/2

does the same. I know not everyone has YEARFRAC -
I was just playing with it... I'm very sorry.