Others

How to Track Employee Leave Balances in SharePoint Lists

Adam DavisAdam Davis Oct 29, 2026 870 views

Question details

The user wants to track and calculate the total approved, pending, and rejected leave days for each employee.

How to Track Employee Leave Balances in SharePoint Lists
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.
Before you start

Ensure you have Edit or Site Owner permissions for the SharePoint site and access to Power Automate with an active Microsoft 365 account.

Solution 1Recommended

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.

1
Create a Calculated Column in SharePoint

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'.

2
Build a Power Automate Flow

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.

3
Filter and Summarize Totals

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.

Calculate Leave Totals with SharePoint Lists and Power Automate
Working Day Calculations: SharePoint calculated columns do not natively exclude weekends. For accurate working day calculations, consider using an advanced Power Automate expression to subtract weekends and public holidays.
Free Microsoft Office alternative

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. 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or open your existing Excel leave tracking template.
  2. 2. Set Up Tracking Columns: Create clear columns for Employee Name, Start Date, End Date, Approval Status, and Total Days.
  3. 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.
Free to use with a familiar, user-friendly interface.Seamlessly compatible with Microsoft Excel (.xlsx) formats.Easily calculate leave balances using SUMIF and NETWORKDAYS formulas.Lightweight application that runs smoothly across Windows, Mac, and Linux.
QA img-9

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.