Solved

Calculate time not logged in

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

Expert Comment

by:byundt
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 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

10 Experts available now in Live!

Get 1:1 Help Now