How to Track Employee Leave Balances in SharePoint Lists
Question details
The user wants to track and calculate the total approved, pending, and rejected leave days for each employee.

- Product
- SharePoint and Power Automate
- Device & OS
- not provided
- Scenario
- Managing employee leave requests and calculating remaining balances based on approval status.
- Observed behavior
- A workflow is needed to automatically calculate leave durations and categorize them based on the request status.
Ensure you have Edit or Site Owner permissions for the SharePoint site and access to Power Automate with an active Microsoft 365 account.
Calculate Leave Totals with SharePoint Lists and Power Automate
Use a calculated column in SharePoint to evaluate leave duration and a Power Automate flow to filter statuses and sum balances per employee.
By combining SharePoint's built-in calculated columns with Power Automate's data operations, you can dynamically track leave balances without needing complex custom applications.
Navigate to your SharePoint List settings and add a new Calculated Column. Set the formula to calculate the date difference between the End Date and Start Date. To handle rejected requests, wrap the calculation in an IF statement to return a zero value if the Status column equals 'Rejected'.
Open Power Automate and create a scheduled or trigger-based flow. Add the 'Get items' action to retrieve all records from your SharePoint leave request list.
Use an OData filter query or the 'Filter array' action to group the retrieved requests by employee and status (Approved, Pending, Rejected). Finally, use variables within an 'Apply to each' loop to sum up the calculated leave durations for each category.

Track Employee Leave Balances Easily with WPS Spreadsheet
While SharePoint and Power Automate offer complex enterprise workflows, WPS Spreadsheet provides a straightforward, free, and highly compatible alternative for tracking employee leave balances. You can easily manage requests and calculate working days using built-in formulas without an expensive Microsoft 365 subscription.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or open your existing Excel leave tracking template.
- 2. Set Up Tracking Columns: Create clear columns for Employee Name, Start Date, End Date, Approval Status, and Total Days.
- 3. Apply Calculation Formulas: Use the built-in NETWORKDAYS formula to calculate exact working days between dates, and use SUMIFS to total the approved leave days per employee dynamically.

Frequently Asked Questions
Can I track leave balances in SharePoint without using Power Automate?
Yes, you can use the Grouping and Totaling features directly in a SharePoint List view. Group the list by the Employee Name column and set the Calculated Column to display a 'Sum' total. However, this won't automatically update a separate summary list without manual export or Power Automate.
How do I exclude weekends from the SharePoint leave duration calculation?
SharePoint calculated columns cannot easily exclude weekends natively. It is highly recommended to use a Power Automate expression to filter out weekends, or use a spreadsheet tool like WPS Spreadsheet with the NETWORKDAYS formula for accurate calculations.
Why is my SharePoint calculated column showing a syntax error?
Errors typically occur due to incorrect comma or semicolon usage in your region, or referencing empty date fields. Ensure your IF statements are properly formatted and that both the Start Date and End Date fields are set to 'Required' before saving the calculation.




