How to Create a Pivotable Table to Capture the Total Time of the Employee’s Late

If you want to capture the total number of minutes late of the employees attendance, you can use the Pivotable Attendance Report. This report has been flattened out so that you can use more spreadsheet operation on it such as generating a Pivot table of your employees attendance.

First, you need to extract the Pivotable Attendance Report. Click here to see how.

nce generated, you can import it to a Google Document if you want.

  • Open the sheet
  • Hide unnecessary columns if you want
  • Change the format of the columns Scheduled Clock In, Scheduled Clock Out, Actual Clock In and Actual Clock Out to TIME
  • Insert 2 Column after the Actual Clock Out column and name it Lates & Lates (Time)

Untitled_Clipping_011917_030450_PM

Generate the Formulas

Lates
  • For the LATES, select one cell under the Lates column then use this formula – “=If(Actual Clock In>Scheduled Clock In,1,0)”
  • Hit Enter
  • See screenshot for sample

Untitled_Clipping_011817_114946_AM

  • Copy the cell and paste it for all cells under Lates column
Lates (Time)
  • For the LATES, select one cell under the Lates column then use this formula – “=If(Lates=1,Actual Clock In–Scheduled Clock In,0)”
  • Hit Enter
  • See screenshot for sample

Untitled_Clipping_011917_031025_PM

  • Update the Format of cell to Duration
  • Copy the cell and paste it for all cells under Lates (Time) column

Pivot the Data

  • Select all Cells
  • Under Data, choose Pivot Table
  • A new sheet will be created named Pivot Table
  • On the Report Editor:
    • Rows – Add the field NAME
    • Values – Add the field LATES (TIME)

Screenshot_011917_031307_PM

Your Pivot Table would appear like this:

Screenshot_011917_033148_PM

 

Now that’s how you figure out the total time your employees are late using the Pivotable Attendance Report. If you have questions or suggestions, feel free to contact us at support@payrollhero.com.

Related Articles