'Time to Fix' Formula

[wlln001]

I am trying to create a formular which will exclude out of hour support
time..

Here's an example

Device A fails at 25/08/05 05:20:00 and recovers on 30/08/05 11:20:00

The total down time is 126:00:00

However

The total fix time is 61:20:00 because the hours between 18:00:00 -
05:59:59 are excluded from the time to fix figure for each day..

I need a formular which will look at the Fail time and the recovery
time and produce a Fix time, rather than a total down time.

Can anyone help ?????

 

[Robert_Steel]

wlln001
I think I have a formula that will do what you ask.

Device A fails at 25/08/05 05:20:00 and recovers on 30/08/05 11:20:00

The total fix time is 61:20:00 because the hours between 18:00:00 -
05:59:59 are excluded from the time to fix figure for each day..

I actually make the answer 65:20:00
5x12hours + 5:20

Defined names
StartDT and EndDT as Fail and Recovery date times
DayStart and DayEnd are the working time
eg. 06:00:00 and 18:00:00

=((INT(EndDT)-INT(StartDT))*(DayEnd-DayStart))
-MAX(MIN(MOD(StartDT,1)-DayStart,(DayEnd-DayStart)),0)
+MAX(MIN(MOD(EndDT,1)-DayStart,(DayEnd-DayStart)),0)

I am sure the logic could be simplified but this seems to work.
There are some very good generic formulas that can be found with a Google
search for Working Time
that will allow excluding weekends and holidays using NETWORKDAYS.

hth RES