Calculate time not logged in

The following is an export from a telephone system.  
It shows times the person logged into the phone system, and times that they were logged out.
The shift for this person is 9-30 - 6  m-f

I need an fast way to produce a report that shows the range of times not logged-in within that persons defined shift, along with totals for each time frame in terms of hours and minutes not logged in.

Assistance is greatly appreciated.
141230.xlsx
frugalmuleAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
I incorrectly assumed the shift began at 9:00 AM. If it begins at 9:30 AM, then change lines 10:14 of the code to:
        'Use either the next statement (Excel 2010 or later) or the two that follow it (Excel 2007)
    'rgdata.Columns(5).FormulaR1C1 = _
        "=RC[-2]-IFERROR(AGGREGATE(15,6,R1C[-1]:R[-1]C[-1]/((R1C[-4]:R[-1]C[-4]=RC[-4])*(R1C[-3]:R[-1]C[-3]=RC[-3])),1),TIME(9,30,0))"
    rgdata.Cells(1, 5).FormulaArray = _
        "=RC[-2]-MAX(IFERROR(MIN(IF((R1C[-4]:R[-1]C[-4]=RC[-4])*(R1C[-3]:R[-1]C[-3]=RC[-3])=1,R1C[-1]:R[-1]C[-1],"""")),0),TIME(9,30,0))"
    rgdata.Cells(1, 5).AutoFill Destination:=rgdata.Columns(5)

Open in new window

DowntimerQ28588750.xlsm
0
 
byundtCommented:
I think you could use a PivotTable type of report to summarize by the amount of "logged out" time for each extension.

If you sort the data in ascending order by date, extension & time, you can calculate the amount of logged out time preceding each session using a formula like:
=C2-IFERROR(AGGREGATE(15,6,D$1:D1/((A$1:A1=A2)*(B$1:B1=B2)),1),TIME(9,0,0))
The preceding formula requires Excel 2010 or later. Please advise if you are using an earlier version of Excel, as alternative formulas are possible.
DowntimerQ28588750.xlsm
0
 
byundtCommented:
If you use Excel 2007, you can use an array-entered formula instead of the previously suggested AGGREGATE formula:
=C2-MAX(IFERROR(MIN(IF((A$1:A1=A2)*(B$1:B1=B2)=1,D$1:D1,"")),0),TIME(9,0,0))

Here is code that illustrates how to use both formulas to produce a PivotTable report:
Sub Downtimer()
Dim rg As Range, rgdata As Range
Application.ScreenUpdating = False
With ActiveWorkbook.Worksheets(1)
    .Range("E1") = "Downtime"
    Set rg = .Range("A1").CurrentRegion
    Set rgdata = rg.Cells(2, 1).Resize(rg.Rows.Count - 1, 5)
    
        'Use either the next statement (Excel 2010 or later) or the two that follow it (Excel 2007)
    'rgdata.Columns(5).FormulaR1C1 = _
        "=RC[-2]-IFERROR(AGGREGATE(15,6,R1C[-1]:R[-1]C[-1]/((R1C[-4]:R[-1]C[-4]=RC[-4])*(R1C[-3]:R[-1]C[-3]=RC[-3])),1),TIME(9,0,0))"
    rgdata.Cells(1, 5).FormulaArray = _
        "=RC[-2]-MAX(IFERROR(MIN(IF((R1C[-4]:R[-1]C[-4]=RC[-4])*(R1C[-3]:R[-1]C[-3]=RC[-3])=1,R1C[-1]:R[-1]C[-1],"""")),0),TIME(9,0,0))"
    rgdata.Cells(1, 5).AutoFill Destination:=rgdata.Columns(5)
    
    rgdata.Columns(5).NumberFormat = "h:mm:ss;@"
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=rgdata.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=rgdata.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=rgdata.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange rg
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
PTmaker rg
End Sub

Sub PTmaker(rg As Range)
Dim shName As String
Dim wb As Workbook
With rg.Worksheet
    Set wb = .Parent
    shName = "'" & .Name & "'!"
    wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=shName & rg.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable _
        TableDestination:=shName & "R1C8", TableName:="PivotTable1"
    With .PivotTables("PivotTable1")
        With .PivotFields("Extn")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Date")
            .Orientation = xlRowField
            .Position = 1
        End With
        .AddDataField .PivotFields("Downtime"), "Sum of Downtime", xlSum
        With .PivotFields("Downtime")
            .Orientation = xlRowField
            .Position = 2
        End With
        .DataBodyRange.NumberFormat = "h:mm:ss;@"
    End With
End With
End Sub

Open in new window


A sample workbook using the code is attached. As written, the code applies to the active workbook (i.e. the macro may be located in your Personal.xlsm or other workbook).
DowntimerQ28588750.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.