SLA - Downtime calculation

[darasingha]

I am trying to calculate downtime for a Service Level Agreement.

The data that I have is the start date/time and the resolved date/time
for an incident.

The data are in the format - 1/1/2008 03:32 AM. The incidents may
occur at any time but downtime is calculated only business hours and
excludes weekends. I may be required to exclude holidays but that is
not a hard requirement right now. What I must be able to specify are
the working hours.

As example(s)
- if an incident is generated on 1/2/2008 4:00 PM and resolved at
1/3/2008 11:00 AM then the downtime is 4 hours.
- if an incident is generated on 1/12/2008 4:00 PM (which is a Sat)
and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime
is 3 hours. This is important for incidents generated on off hours
the clock does not start until the next working hour. Also, any
incidents resolved during off hours are counted back against the last
working hour.

The above assumes working hours are 8:00 AM through 5:00 PM.

I have tried using the NETWORKDAYS and WORKDAY functions with little
success.

Any suggestions or tips ?

TIA,

RM.

 

[Ker_01]

I suspect this would be dramatically easier to do in straight VBA, but since
you mentioned the formulas I started wondering what it would take to do this
with just formulas. My guess is that one of the gurus on this board could
probably figure out a much more eloquent solution, but here are a few
pointers to get you started in the right direction:

A1 = Start date/time
A2 = End date/time

=(NETWORKDAYS(A1,A2)-1)*8
gives you the number of hours, subtracting one as not to count 'same-day'
fixes

=SUM(MOD("5:00:00 PM",1)-MOD(A1,1),MOD(A2,1)-MOD("8:00:00 AM",1))
gives you the remaining hours for partial days. However, this also
calculates a value if there is a same-day fix (which you don't want) so
you'd have to put in an IF statement to check if the date of A1 and A2 are
the same.

Then there is probably a transformation required in order to add them
together; maybe instead of multiplying the first one by 8, you should
multiply by 24/8 (.33) to get the number of hours in a time-friendly format
that can be added to the second value.

HTH,
Keith