Leave Balance Tracker in Excel: Entitlement, Used, Remaining and Balance Value

Every employee asks: how much leave do I have left? And every HR manager answers by opening scattered files, so the answer is slow and mistakes repeat. Worse, accumulated balances are a liability that suddenly appears at end of service.

This sheet calculates the balance straight from the request log: it counts working days between start and end dates excluding Friday, Saturday and public holidays, adds carry-over and the amount accrued to date, subtracts what was used and estimates the value of the balance in riyals so you know your liability.

Who is it for?

• HR officers in small and medium businesses
• Department managers who approve requests
• Business owners who want to know leave liabilities
• Accountants who calculate end-of-service dues

What is inside the sheet?

• A balances sheet for twelve staff with room to grow to thirty-two
• A request log with leave type, dates and status
• Working-day counting that skips Friday, Saturday and public holidays
• Automatic entitlement of 21 or 30 days by years of service
• Sick-leave tier: 100% pay, then 75%, then unpaid
• Balance value per employee and in total
• Flags for high and negative balances and who is on leave today

How to use it

1. Enter the report date and edit entitlement days if your policy differs.
2. Add employees with hire date, salary and carry-over.
3. Log each leave request on the log sheet and set its status.
4. Review high balances and pending requests.

Formulas used

• Accrued to date = annual entitlement × (days elapsed in the year ÷ 365)
• Balance = carry-over + accrued to date − used
• Balance value = balance × salary ÷ month days
• Working days = NETWORKDAYS.INTL excluding Friday, Saturday and holidays

Worked example from the sheet itself

Twelve employees with balances worth 28,186 and 141 balance days in total.

• An employee with 7.6 years of service is entitled to 30 days, has accrued 24.3 so far, used 15 and has a balance of 9.3.
• One employee balance of 25.3 days shows as a high balance.
• Four requests are pending and one employee is on leave today.
• Sixteen sick days have been used across the team.

Working days, not calendar days

A leave from Sunday to Thursday is not six days; it contains a Friday and Saturday which are rest days. The sheet uses a function that excludes the local weekend and the public holidays you add, so an employee is not charged a day they would not have worked.

Entitlement and accumulated balance

The Saudi labour law gives annual leave of not less than 21 days, rising to 30 days after five continuous years, and the sheet accrues the balance in proportion to the days elapsed in the year. If an employee does not take leave the balance builds into a liability payable in cash at the end of the relationship.

Sick leave

Sick leave steps down under the law: thirty days at full pay, then sixty at three quarters, then thirty unpaid in a year. The sheet shows the tier each employee has reached so you can see the effect of absence on pay.

Balance value

Converting balance days into riyals makes the conversation with management clearer: postponing leave is not saving but a growing obligation. You set the month days used in the daily-rate calculation.

Tips for best results

• Check the balance before approving a request.
• Encourage staff with high balances to schedule leave.
• Add public holidays every year.
• Keep a monthly copy of the log.

Frequently asked questions

Does it support a Friday-Saturday weekend?

Yes, it is the default weekend in the calculation.

How do I handle half days?

Record a full day or adjust the days manually per your policy.

Are the numbers legally binding?

No, it is a management tool; refer to the labour law and your company policy.

Download the sheet

Press the download button at the top of the page to get the Excel file. It includes a full guide sheet, yellow input cells and formulas protected from accidental edits. The sheet is also available in Arabic from the language switcher.

Entitlements follow the Saudi labour law and are editable; this is a management tool, not legal advice.