Solved

Calculate time not logged in

Posted on 2014-12-30
3
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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