Getting a list of employees with more than 10 days of absences

Set the Absence sheet to include the dates needed.

Click on the refresh button.

Click on the Absence Pivot Table tab at the bottom of the sheet

If needed, click on the Filter button in the Absence Reason column and select the absence codes desired.

Drag the Absence Reason to the top of the report.

Drag all of the hours and day fields up and to the right to drag them off the table until you only have either the Actual Days or Calc. Actual Days left.

You should only have the Site Loc, Full Name and Actual Days (or Calc. Actual days) left.

 Click on the filter button for the Full Name and select More Sort Options.

Select Descending by and then drop down the selection and pick the Sum of Actual Days (or Calc. Actual Days). This will sort each location with the highest absences at the top.

To narrow the report down to certain job codes, click on the filter button for the job codes at the top (be sure to “Select Multiple Items.”

To narrow the list down to only entries with more than 10 days, you can create a calculated column to the right of the absence days: Enter a heading next to the heading for the days and title it “More than 10 dyas?”

Put a formula in the first cell under the new heading. Assuming that the first entry uner the Calc. Actual Days (or Actual Days) is cell C28, then the formula would be

=if(C28>=10, “Y”, “N”)

This formula says that if the information in cell C28 (where the actual day is located) is greater than or equal to 10, then put a Y in the cell, otherwise put an N in the cell.

If your formula worked, then you should have a Y or N in the new column.

Now – copy the formula down – TIP – click in the cell where the new formula is. Double click on the dot in the right bottom of the cell.

It will copy the formula down for you.

So that we can filter in the pivot table, let’s remove the total line for location (we don’t want the lines with the totals to be in the filtered list), right click on the Site Loc header and select Field Settings.

Select None for the Subtotals and click on OK.

Now click in the header for the new column.

Click on Data, Filter

Select only the Y entries (entries with 10 or more days absent)

This will give you the list of people with more than 10 days of absence by location!