2
\$\begingroup\$

My input data comes in the following tabular table:

enter image description here

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

enter image description here

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Snowbody Dec 10 '17 at 2:36
  • 1
    \$\begingroup\$ Is there a reason you can't just convert everything to timestamps, subtract and convert back? \$\endgroup\$ – Oscar Smith Dec 11 '17 at 21:13
4
+50
\$\begingroup\$

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).

\$\endgroup\$
  • \$\begingroup\$ 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! \$\endgroup\$ – Fernando J. Rivera Dec 11 '17 at 23:51
2
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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.

enter image description here

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.

enter image description here

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.

enter image description here

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"
        .AddDataField .PivotFields("time"), "Salida", xlMax
        .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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.