Network Outage Calculator

RogersRetail
RogersRetail used Ask the Experts™
on
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?
Comment
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?

Author

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

Commented:
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:

http://www.cpearson.com/excel/DateTimeWS.htm

Download the first example in zip format, and inside there is an example XLS sheet (http://www.cpearson.com/Zips/DateInterval.zip)

Hope this helps,
NY

Author

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

Does this close the question for you?

Thanks,
NY

Author

Commented:
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 http://www.cpearson.com/excel/DateTimeWS.htm

Author

Commented:

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
Commented:
Hello RogersRetail,

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

=WorkingHrs(A3+B3,C3+D3,H3,I3,23456)

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

=WorkingHrs(A3+B3,C3+D3,H3,I3,234567)

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.

Regards,

Patrick
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
    Next
    
    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")
            Next
        Next
    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
        Else
            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
    Next
    
    ' convert days to hours
    WorkingHrs = WorkingHrs * 24
    
Cleanup:
    On Error GoTo 0
    Set Dict = Nothing
    
End Function

Open in new window

Author

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

Commented:
RogersRetail,

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

Patrick

Author

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

Top Expert 2010

Commented:
RogersRetail,

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.

Cheers,

Patrick

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