# Time table where workers overstay past midnight

My input data comes in the following tabular table:

With this, I make a basic time table where I record arrival hour, leaving hour and total work hours.

Problem is, I can't do it with a simple pivot table because sometimes people will stay past midnight, so if I just get the MIN(Time) I'll sometimes get that the employee arrived at 1am. Another issue is I need to use the STATE to determine if it's a user arriving or leaving (denoted by "* Entrada" for arrivals and "* Salida" for leavers).

To work with this, I arbitrarily decided that the corresponding work day starts at 6am, so first I defined the following named ranges:

1. tblState: =OFFSET(data!$A$1,1,MATCH("STATE",data!$1:$1,0)-1,COUNTA(data!$A:$A)-1)
2. tblDate: =OFFSET(data!$A$2,0,0,COUNTA(data!$A:$A)-1)
3. tblName: =OFFSET(data!$A$1,1,MATCH("NAME",data!$1:$1,0)-1,COUNTA(data!$A:$A)-1)

And then my working formulas are

Arrived:

{=MAX((MIN(IF(((tblName=$B10)*(tblDate>(D$8+0.25))*(RIGHT(tblState,7)="Entrada")*(WEEKDAY(D$8,2)<6)*(tblDate<(D$8+1)))>0,tblDate))-D$8),0)}  Left: {=MAX((MAX(IF(((tblName=$B10)*(tblDate<(D$8+1.25))*(tblDate>(D$8+0.25))*(RIGHT(tblState,6)="Salida"))>0,tblDate))-D$8),0)}  Hours (I do this to take care of negatives and to substract the lunch hour from the working hours if the employee worked more than a half shift): =MAX((IF(OR(D10=0,D11=0),0,IF((D11-D10)<(4/24),D11-D10,MAX(D11-D10-(1/24),0)))),0)  It is incredibly inelegant and inefficient — it takes about 5 mins to calculate with ~25k records on my data table. Another issue is that for the next month, I have to manually check for new records on the data table (add new employees to the time table) and missing records (remove employees who no longer work for the company), though I figure I could easily take care of this with a macro. What would be a more efficient alternative? • This might be a situation where Excel is not the best tool for the job. I think VBA would be a good way to prototype it, but eventually you'll have to move to a "real" database, or maybe one of the hybrids like Airtable. – Snowbody Dec 10 '17 at 2:36 • Is there a reason you can't just convert everything to timestamps, subtract and convert back? – Oscar Smith Dec 11 '17 at 21:13 ## 3 Answers It can be tough to come up with a definitive solution without having your data, but here's a few ideas that I would consider if I was having to solve this problem. Since you have the full date and time in your data table, you could store that in your time table, and just display the time part of the date/time. Then your elapsed time calculations would be simplified because you can use the difference between arriving and leaving that you're already calculating in the sheet. Alternatively, if you could change your time table to allow multiple entries in one day for each person, you could have the person "leave" at midnight (23:59:59 or 24:00:00) the day the arrive, and "arrive" at 00:00:00 the next day. This would still require some complexity in the time calculations. If those ideas won't work for you, you can make a small improvement in your "hours" calculation. The last part of it (MAX(D11-D10-(1/24),0)) is overly complicated. You've just checked that (D11-D10)<(4/24), you know that D11-D10 will be >= 4/24, so the MAX calculation could just be D11-D10-(1/24). • I can't store the full date+time and display only the hours, because I need to do full month calculations (monthly average, for example). If I store the full date+time I'd have to trunc out the date later to average the hours. The behaviour you described is the default when working with pivots - if there's data at 2am, it takes it as an "arrival" at 2am on the next day. You're right with the MAX, I can't believe I missed that, thanks! – Fernando J. Rivera Dec 11 '17 at 23:51 A more efficient alternative would be to just convert the times to military time. This way you can easily set up MAX and MIN, do your easy math, then convert them back into normal times when constructing your table. If you have any negative values, just add them back onto 24. Ok, so in the end I figured adding a couple values to the data table was the easiest approach. I added 2 columns - date and time: Date: =IF((A2-TRUNC(A2))<0.25,TRUNC(A2)-1,TRUNC(A2)) Hour: =A2-B2 That way when a worker stayed past midnight of day X, but before 6 AM of the following day X+1, it recorded X in the Date field, even though in the original Time record it had a day value of X+1. That way, it won't count as the min. With this, I did a tabular pivot with the following fields: Arrived: =MIN(Hour) Left: =Max(Hour) And manually added the Hours field: =D4-C4 '(Left - Arrived) I did not need aditional logic here because I already handled the negatives and the over-midnight working in the Date field. Then, I did a double pivot on this new data table and that produced my desired results, with the added bonus of automatically having all the workers updated and the monthly average. Finally, I wrote a macro (with help from a couple answers on StackOverflow) to do all these steps automatically: Sub testy() Dim n As Long Dim PvtTbl As PivotTable Dim pvtFld As PivotField Dim wkb As Workbook Dim ws As Worksheet, temp As Worksheet Set wkb = Application.Workbooks.Open(GetImportFileName) With wkb.Worksheets(1) n = .Cells(.Rows.Count, "A").End(xlUp).Row .Columns("B:B").Insert Shift:=xlToRight .Columns("B:B").Insert Shift:=xlToRight .Cells(1, 2).Value = "date" .Cells(2, 2).Resize(n - 1, 1).FormulaR1C1 = "=IF((RC[-1]-TRUNC(RC[-1]))<0.25,TRUNC(RC[-1])-1,TRUNC(RC[-1]))" .Cells(1, 3).Value = "time" .Cells(2, 3).Resize(n - 1, 1).FormulaR1C1 = "=RC[-2]-RC[-1]" End With Set temp = wkb.Sheets.Add(After:=wkb.Sheets(wkb.Sheets.Count)) temp.Name = "Temp" wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "data!R1C1:R" & n & "C10", Version:=6).CreatePivotTable TableDestination:= _ "Temp!R3C1", TableName:="tempData", DefaultVersion:=6 Set PvtTbl = temp.PivotTables(1) With PvtTbl .PivotFields("Nombre").Orientation = xlRowField .PivotFields("Nombre").Position = 1 .PivotFields("date").Orientation = xlRowField .PivotFields("date").Position = 2 .PivotFields("date").NumberFormat = "d/m/yyyy" .AddDataField .PivotFields("time"), "Llegada", xlMin .PivotFields("Llegada").NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
.PivotFields("Salida").NumberFormat = "[$-x-systime]h:mm:ss AM/PM" .ColumnGrand = False .RowGrand = False .RowAxisLayout xlTabularRow .RepeatAllLabels xlRepeatLabels .PivotFields("date").Subtotals(1) = False .PivotFields("Nombre").Subtotals(1) = False .PivotFields("Nombre").PivotItems("(blank)").Visible = False End With With temp .Range("E3").Value = "Horas" .Range("E4").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 3, 1).FormulaR1C1 = "=RC[-1]-RC[-2]" n = .Cells(.Rows.Count, "A").End(xlUp).Row End With Set ws = wkb.Sheets.Add(After:=wkb.Sheets(wkb.Sheets.Count)) ws.Name = "Report" wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "temp!R3C1:R" & n & "C5", Version:=6).CreatePivotTable TableDestination:= _ "Report!R3C1", TableName:="reportData", DefaultVersion:=6 With ws.PivotTables("reportData") .PivotFields("Nombre").Orientation = xlRowField .PivotFields("Nombre").Position = 1 .PivotFields("date").Orientation = xlColumnField .PivotFields("date").Position = 1 .AddDataField .PivotFields("Llegada"), "Promedio de Llegada", xlAverage .AddDataField .PivotFields("Salida"), "Promedio de Salida", xlAverage .AddDataField .PivotFields("Horas"), "Promedio de Horas", xlAverage .PivotFields("Promedio de Llegada").NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
.PivotFields("Promedio de Salida").NumberFormat = "[$-x-systime]h:mm:ss AM/PM" .PivotFields("Promedio de Horas").NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
.DataPivotField.Orientation = xlRowField
.DataPivotField.Position = 2
.RowAxisLayout xlTabularRow
End With

End Sub

Function GetImportFileName() As String
Dim Finfo As String
Dim FilterIndex As Long
Dim Title As String
Dim FileName As Variant
'  Set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"
'  Display *.* by default
FilterIndex = 5
'  Set the dialog box caption
Title = "Select a File to Import"
'  Get the filename
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)
'  Handle return info from dialog box
If FileName = False Then
GetImportFileName = ""
Else
GetImportFileName = FileName
End If
End Function