Solved

Calculate time not logged in

Posted on 2014-12-30
3
59 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 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