Employee Attendance Record Template in Excel

Hi Shannen C.,

Thank you for your update, if my answer is helpful, please mark it as an answer, which will definitely help others in the community who have similar queries to find solutions to their problems faster. 

Best regards, 

Jay-Tr - MSFT | Microsoft Community Support Specialist

Hi Shannen C., 

Thank you for reaching out. To calculate total leave used and remaining PTO in your Employee attendance record.xlsx file, please follow the instructions below. These steps will help you create a summary table that includes all leave types (Vacation, Sick leave, Bereavement, Other) and determine how much PTO each employee has left. 

1. Create a Summary Table

In a new sheet or at the bottom of the Employee Leave Tracker sheet, create a table with the following headers: 

You can adjust the PTO Allowed column if different employees have different entitlements. 

1. Calculate Total Leave Used

In the Total Leave Used column (e.g., cell B2), enter the following formula:  

• =SUMIF('Employee Leave Tracker'!B:B, A2, 'Employee Leave Tracker'!F:F)
• 'Employee Leave Tracker'!B:B refers to the Employee name column.
• A2 is the employee name in your summary table.
• 'Employee Leave Tracker'!F:F refers to the Days column.

This formula sums all leave days (regardless of type) for the employee listed in A2. 

1. Calculate Remaining PTO

In the Remaining PTO column (e.g., cell D2), enter: =C2-D2 

• C2 is the PTO Allowed.
• B2 is the Total Leave Used.

This will subtract the total leave used from the allowed PTO to show how many days remain. If the result is negative, it means the employee has exceeded their PTO allowance. 

1. Step 4: Copy the Formulas Down

Drag the formulas in columns B and D down for each employee row in your summary table. 

Here is the final result in Employee attendance record.xlsx file: 

If you have any further questions and concerns, please don’t hesitate to ask. Looking forward to your response. 

Best regards, 

Jay-Tr– MSF T | Microsoft Community Support Specialist

Hi Shannen C., 

Thank you for the clarification. As you are using a template differs from mine, could you please share the template so that I can test for more possible solutions and send you the template back?

Best regards, 

Jay-Tr - MSFT | Microsoft Community Support Specialist

Hi Shannen C., 

Good day to you! Thank you for posting in Microsoft Community. 

Based on your request, you're using the Employee Attendance Record Excel template and want to calculate PTO Days Remaining for each employee in the Calendar View tab. You’ve already entered PTO Days Allowed in the List of Employees tab and replaced the “Working Days” section with a PTO tracker. Here's how to complete the setup: 

1. Ensure Your Data Is Structured Properly 
   1. In the List of Employees tab:

• Column A: Employee Name (e.g., Alice, Ben, Clara)
• Column B: PTO Days Allowed (e.g., 15, 20, 10) 
  1. In the Calendar View tab:
• Each row represents one employee.
• Columns D to J (or more) represent daily attendance entries.
• PTO days are marked with the text "PTO".

1. Create the Calendar View Table

Here’s a sample layout you can use: 

1. Explanation of Formulas 
   1. PTO Used: =COUNTIF(D2:J2, "PTO") 
      This counts how many days are marked as "PTO" for the employee in that row.
   2. PTO Days Remaining: =VLOOKUP(A2, 'List of Employees'!A:B, 2, FALSE) - B2 
      This looks up the total PTO allowed for the employee and subtracts the PTO used.

Make sure the employee names in both tabs match exactly. You can copy these formulas down for each row. 

If you have any further questions and concerns, please don’t hesitate to ask. Looking forward to your response. 

Best regards, 

Jay-Tr– MSF T | Microsoft Community Support Specialist