# 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
Microsoft Excel

Last Comment
byundt
byundt

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
byundt

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
With .Sort
.SetRange rg
.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 & "'!"
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
``````

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
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY