Solved

Calculate time not logged in

Posted on 2014-12-30
3
53 Views
Last Modified: 2015-01-04
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
0
Comment
Question by:frugalmule
  • 3
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 40524859
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
 
LVL 81

Expert Comment

by:byundt
ID: 40524913
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
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 40524919
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now