How to Create an Attendance Tracking and Payroll Report in SharePoint
Question details
The user needs to set up a SharePoint Online attendance system to track temporary employees, record daily work, send data to payroll, and summarize totals for management.
- Product
- SharePoint Online
- Device & OS
- not provided
- Scenario
- Creating an integrated attendance and payroll tracking system for temporary staff where employee entries can be logged and counted automatically.
- Observed behavior
- Requires a structured data solution. Standard calculated columns cannot directly count entries in a Person or Group field, requiring alternative logic or automation.
Ensure you have Site Owner or Edit permissions in the target SharePoint Online site to create new lists, modify site columns, and configure Power Automate flows if automation is required.
Set Up a Custom SharePoint List with Content Types
Create a dedicated SharePoint list utilizing custom content types to organize employee and attendance data effectively while avoiding calculated column limitations.
SharePoint lists are excellent for tracking daily work. By enabling content types, you can group related fields (like Employee Name, Hours Worked, and Date) into reusable templates.
Since a 'Person or Group' column cannot be used directly in a calculated column to count employees, you should rely on standard Number or Text columns for calculations, or use custom automation.
Navigate to your SharePoint site homepage, click 'New' > 'List', and select 'Blank list'. Give it a relevant name like 'Temporary Staff Attendance'.
Click the gear icon (Settings) in the top right, select 'List settings', and navigate to 'Advanced settings'. Set 'Allow management of content types?' to 'Yes' and click 'OK'.
Under 'List settings', scroll down to 'Columns' and create necessary fields such as 'Date' (Date and Time) and 'Hours Worked' (Number). Avoid using the 'Person or Group' type if you need to perform direct counting within a calculated column.
Click on the newly exposed 'Content Types' section in List settings to add or modify your columns, ensuring a standardized form when management or payroll enters data.
Use Power Automate to Count Employees and Calculate Totals
Since SharePoint calculated columns cannot process Person or Group fields, use Microsoft Power Automate to extract and count these values automatically.
Manage Attendance and Payroll Effortlessly with WPS Office
Struggling with complex SharePoint list limitations? WPS Spreadsheet provides a lightweight, free, and highly compatible alternative to Microsoft Office. Easily manage employee attendance, automate payroll calculations using standard formulas, and export reports without the need for advanced SharePoint automation.
- 1. Open WPS Spreadsheet: Launch WPS Office and select 'Spreadsheet' to create a new workbook.
- 2. Choose a Template: Click on 'Templates' and search for 'Attendance' or 'Payroll' to use a pre-formatted, professional template.
- 3. Calculate Automatically: Enter your employee data and use built-in functions like COUNTA or SUM to easily bypass the calculation limits found in SharePoint lists.
- 4. Save and Share: Save the file in .xlsx format and securely share it with management or the payroll department.
Frequently Asked Questions
Why can't I count a Person or Group column using a calculated column in SharePoint?
SharePoint calculated columns do not support complex field types like Person, Group, Lookup, or Multiple Lines of Text. You must use Power Automate or custom backend logic to count these entries and store the result in a standard Number column.
How do I enable Content Types in a SharePoint list?
Navigate to your list, click the gear icon, and select 'List settings'. Under 'General settings', click 'Advanced settings'. Set 'Allow management of content types?' to 'Yes' and click 'OK'.
Can I export my SharePoint attendance tracking list for offline payroll processing?
Yes. From your SharePoint list view, click 'Export' in the top command bar and select 'Export to Excel' or 'Export to CSV'. This downloads a query file that opens in your spreadsheet software to analyze payroll data offline.
