Calculate Business Hour Downtime Excluding Weekends

  • 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.


    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.


    TIA,


    RM.

  • Re: Service Level Agreement - Downtime Calculation


    How About this?


    It doesn't calculate holidays

  • Re: Service Level Agreement - Downtime Calculation


    That seems to work as long as the start time is within the "workday". Take the following case
    start time 1/6/2008 11:45 PM (Sun which is a holiday)
    end time 1/23/2008 3:40 AM (which is outside business hrs).


    Given the above the actual downtime should be 96 hrs (7th through 11th=40 hrs + 14th through 18=40hrs+ 21st through 22nd=1 hrs)= 96 hrs.

  • Re: Service Level Agreement - Downtime Calculation


    We Have some sort of misunderstanding. 8 through 5 I calculated as 9 hours and it looks like you are calculating 8. So I am assuming you are giving one hour for lunch. Is it alwayes 12-1?

  • Re: Service Level Agreement - Downtime Calculation


    I am sorry that was my error.


    However, here is another case where the downtime should be 0 -


    Start Time 1/1/2008 1:41 AM (after hours)
    End Time 1/1/2008 3:32 AM (after hours)


    comes out to 1.85.


    thanks for your help.

  • Re: Service Level Agreement - Downtime Calculation


    Sorry - my original assertion of 8-5 i.e. 9 hr day should have said 9-5 hence the 8 hr day. But i think 8-5 works. So no change needed for that.


    Appreciate your help.

  • Re: Service Level Agreement - Downtime Calculation


    Well…
    Since I worked on the script before you said 8 to 5 for a 9 hour day is fine, it works as 8 to 5 with an hour off between 12 and 1. Here is the code.


    Read it and try to understand what it is doing. To change the hours of the day you have to change the variables in Move2Next, IsWorkTime & CalculateEnd.
    Move2Next takes one date/time variable and moves it to the beginning of the next work period.
    IsWorkTime is a function that takes a date/time variable and returns true if it is within a work period and false if it is not in a work period.
    CalculateEnd takes one date/time variable that needs to be within a working period and returns the end time for that working period.
    CalculateDownTime takes the start time and end time. In the beginning it initializes the summing variable result and if start time is not within a working period, it moves it to the start of the next working period. Then it gets the end time for that period, and adds the difference to the result. Moves Start time to the start of the next work period and starts over until it passes end time.

  • Re: Service Level Agreement - Downtime Calculation


    Thanks - worked. I changed it o eliminate the working hours and moved the work times to hidden fields. I will work on adding the holidays next.


    Your help is much appreciated.

  • Re: Calculate Business Hour Downtime Excluding Weekends


    Might be a little late for you but anyway......


    You can do this with a formula approach. Assuming start time/date in A2 and end time/date in B2


    =(NETWORKDAYS(A2,B2)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),J$3,J$2)


    where J2 contains your Monday to Friday work start time (i.e. 09:00 in your case) and J3 the work end time (17:00)


    format result cell as [h]:mm


    You can easily adapt this to take holidays into account. If you have a range of holiday dates just include that range as the 3rd argument of each of the 3 NETWORKDAYS functions

  • Re: Calculate Business Hour Downtime Excluding Weekends


    Hi there, I found this particularly useful for trying to solve my problem of recording server downtime during the business working hours.


    I found that the last example missed minutes and I was unable to update the Business start and end times.


    So I re jigged the code a little to fit my purpose. I hope it helps!


    StartTime and EndTime is the time the server is down.
    WorkStart and WorkEnd are the business start and end times.


  • Re: Service Level Agreement - Downtime Calculation


    Hi I am new one for Exel. I have tried to understand the above coding.But i am not able to understand the CalculateEnd function.
    Could you please explain that part.
    Thank you