Network Outage Calculator

RogersRetail used Ask the Experts™
I've been trying to create, without much sucess, or locate an outage calculator where you can enter in the outage start and end time, the store operating hours, and then determine how much of the operating hours were affected by the outage.  Store operating times are 7 days a week with variances in store open and close times.  Any suggestions?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
We could refine on what you already have. Your question is too general. Can you post what you created and maybe specify a bit what is it that you are needing help with?


I can calculate the total outage time but what I'm looking for in the end is only the outage time during business hours.  

I too need an outage calculator that can be used to calculate outage times during business hours. Did anyone manage to find one?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

In this link you will find exactly what you are looking for:

Download the first example in zip format, and inside there is an example XLS sheet (

Hope this helps,


Thanks newyuppie... the file is password protected though, do you have the password?
You dont need When you download, inside there is DateInterval.xls, that is the relevant workbook. Don't need the rest.

Does this close the question for you?



Sorry for my delay in response.  The DateInverval workbook is really close to what I'm needing.  Although it seems to only calculate based on a 8 hour work day.  Store operating hours are typically around 10 hours or so and therefore would need to include that in the interval calcuation.  

Any ideas on how to adjust that?  I can't figure it out based on what is included in



Also it doesn't take into account weekends, just a standard work week of Monday to Friday.

If you have any ideas on how to make those adjustments to the function that would be great!
Top Expert 2010
Hello RogersRetail,

Add the code below to your VB Project, and then you can use a formula like this:


You can adjust the work days to include Saturday by making it:


Note that this returns the number of hours. If you need to return it as a date/time value, just divide the
result by 24.


Function WorkingHrs(StartAt As Date, EndAt As Date, WorkStart As Date, WorkEnd As Date, Workdays As String, _
    ParamArray Holidays())
    ' Function calculates working hours available within a specified datetime range, allowing for
    ' scheduled working hours, non-working days, and holidays (if desired)
    ' Workdays specifies days employees normally work.  For example, to use Mon - Fri, use 23456.
    ' To do just Tue & Thu, use 35; etc.
    ' assumes scheduled working hrs are the same on each working day!
    Dim Counter As Long
    Dim Dict As Object
    Dim x As Variant
    Dim y As Variant
    Dim Days(1 To 7) As Boolean
    Dim WorkThisDay As Boolean
    Dim HolThisDay As Boolean
    Dim DateToday As Date
    Dim DayStart As Date
    Dim DayEnd As Date
    ' array indicates whether that weekday is a regular workday.  Initialize to False
    Days(1) = False
    Days(2) = False
    Days(3) = False
    Days(4) = False
    Days(5) = False
    Days(6) = False
    Days(7) = False
    ' populate array with results from Workdays argument
    For Counter = 1 To Len(Workdays)
        Days(Val(Mid(Workdays, Counter, 1))) = True
    On Error GoTo Cleanup
    ' populate holiday array
    If Not IsMissing(Holidays) Then
        Set Dict = CreateObject("Scripting.Dictionary")
        For Each x In Holidays
            For Each y In x
                If Not Dict.Exists(Format(y, "m/d/yyyy")) Then Dict.Add Format(y, "m/d/yyyy"), Format(y, "m/d/yyyy")
    End If
    'loop through days in datetime range
    For Counter = Int(StartAt) To Int(EndAt)
        DateToday = CDate(Counter)
        ' determine if regular workday
        WorkThisDay = Days(Weekday(DateToday, vbSunday))
        ' determine if holiday
        If IsMissing(Holidays) Then
            HolThisDay = False
            If Dict.Exists(Format(DateToday, "m/d/yyyy")) Then HolThisDay = True Else HolThisDay = False
        End If
        ' if regular workday and not a holiday, figure out hrs from that day
        If WorkThisDay And Not HolThisDay Then
            ' starts and ends on same day
            If Int(StartAt) = Int(EndAt) Then
                DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
                DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
                WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            ' first day, if first day <> last day
            ElseIf Counter = Int(StartAt) Then
                DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
                DayEnd = WorkEnd
                WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            ' days in between start and end, if any
            ElseIf Counter > Int(StartAt) And Counter < Int(EndAt) Then
                WorkingHrs = WorkingHrs + CDbl(WorkEnd - WorkStart)
            ' last day, if first day <> last day
            ElseIf Counter = Int(EndAt) Then
                DayStart = IIf(CDate(EndAt - Int(EndAt)) > WorkStart, WorkStart, CDate(EndAt - Int(EndAt)))
                DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
                WorkingHrs = WorkingHrs + IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            End If
        End If
    ' convert days to hours
    WorkingHrs = WorkingHrs * 24
    On Error GoTo 0
    Set Dict = Nothing
End Function

Open in new window


I'm hoping for an Excel Solution and not VB.
Top Expert 2010


Have you tried my suggestion?  In my testing, it appears to do exactly what you want.



Matthewspatrick provided a VB solution and I am looking for an Excel solution.

Top Expert 2010


I understand that you preferred a solution that does not use VBA code, but such solutions are not always possible, and this is one of those cases.

I provided an approach that will get you the answer you need.  If you need assistance in implementing that solution, by all means let me know, and I will provide such assistance as I can.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial