Employee Vacation Accrual Template
How much vacation or sick leave have you accrued? How many vacation days will you have 3 months from now? This new employee vacation accrual and tracking template helps you answer these questions. If you are an employer, you can track the benefits accrued for multiple employees, by making a copy of the worksheet for each employee.
This employee vacation accrual and tracking template actually came about due to a request from someone working in human resources at a law firm. Most large companies have their own leave management systems in place, but if you have a small business, this spreadsheet may be just what you need.
Employee Vacation Tracking Templatefor Excel
This vacation accrual spreadsheet also tracks sick leave and personal leave. It can be used for cases in which benefits are accrued monthly, semi-monthly, weekly, or bi-weekly.
The workbook contains two worksheets: one for weekly/biweekly accrual and the other for monthly/semi-monthly accrual (view). I set it up this way because the formulas end up being quite different for the different cases.
Note that this template, in addition to my payroll register, is precisely what I use for recording the hours and benefits for my own employee(s). It works well.
For employees: If you just want a way to track your own vacation and benefits accrual, this free version may be all that you need.
For employers: The unlocked version below will give you more freedom to customize the spreadsheet to meet the needs of your business.
New in Version 1.2: You can now specify maximum accrual limits.
"No Installation, No Macros - Just a simple spreadsheet - An original creation by Dr. Jon Wittwer of Vertex42.com"
Requirements and License agreement same as above.
Rate and Review this Vacation Tracking Template
To view and write reviews, click on the button below.
How to Use this Vacation Accrual Template
Step 1: Choose either the Weekly or Monthly Accrual Worksheet
If your company uses daily vacation accrual, this spreadsheet probably won't work for you. It is set up for weekly, semi-weekly, monthly, or semi-monthly accrual periods.
If you are using a semi-monthly accrual period, use the Monthly Accrual worksheet and change the Pay Period to Semi-Monthly.
Step 2: Enter Total Hours Earned per Year
I would assume that your company's benefits policy states the number of hours of vacation and other benefits an employee can earn each year. The accrual rate is also shown in the worksheets, so if necessary you can manually override the formulas in those cells to enter specific accrual rates.
Step 3: Add the Other Info
Besides the obvious Company Name and Employee Name fields, there are a few other values you'll need to enter.
Weekly Accrual worksheet:
- Pay Period Starting: Enter the date of the start of the first pay period (normally the start of your fiscal year, but for a new employee it may be mid-year).
- Weeks Per Pay Period: For bi-weekly pay periods, enter a 2.
- Accrued Every N Periods & Accrual Starting Period: If the employee vacation accrual period is identical to the pay period, then these values should probably all be 1 (one). However, if your policy states that employee vacation is accrued every 4 weeks, but you have a bi-weekly pay period, you'll need to modify these values.
Monthly Accrual worksheet: This one is pretty simple. Just enter the date of the start of your fiscal year and choose the pay period.
Step 4: Enter Maximum Limits if Applicable
If your policy specifies maximum limits for vacation, sick leave, or personal leave, enter the hour limits in the "Maximum" fields located above the CUMULATIVE columns. The formulas in the EARNED columns are set up to ensure that the balance never exceeds the maximum number of hours. If there are no limits, just leave the Maximum fields blank or enter "n/a".
Step 5: Update Vacation Carry-Over or Starting Balances
This spreadsheet is normally only valid for a single fiscal year because there is usually a limit to how much sick leave, personal leave, or vacation that can be carried over to the next year.
So, at the beginning of the next year, just make a copy of the worksheet and change the beginning balance to reflect the amount carried-over from the previous year.
Vacation Tracking and Leave Accrual for New Employees
Contracts for new employees usually state how much sick leave or vacation the employee starts with. Also, some leave might not start to accrue for 6 months or a year or whatever. To handle these cases, you can try deleting or modifying the formulas in the Earned columns for the non-accrual periods.
Variable Accrual Rates
If your company has a policy in which the amount of vacation or other benefits earned increases based on the time the employee has been with the company, I'd recommend one of the following approaches:
- Start a new worksheet and adjust the carry over balance and accrual rates each time the accrual rates change.
- Delete the formulas in the EARNED columns and enter the accrued amounts manually as needed. (Not an automated approach, but it gives a lot more flexibility)
Example Policy Statements
If you are a new employer, you will probably want to add a statement in your employee agreement or handbook to indicate how vacation, sick leave, and/or personal leave is accrued. The example below shows how to describe the vacation accrual where there is a maximum:
"... Vacation time will accrue at a rate of 4 hours every two weeks not to exceed 120 hours, starting with a balance of 40 hours on the Hire Date...."
Background Color: The highlighting on every other row in the accrual table is controlled using Conditional Formatting. If you need to manually change the background color, you'll need to remove the conditional formatting first.
Inserting/Deleting Rows: The vacation accrual formulas are set up to allow you to easily insert or delete rows. You can also extend the accrual table by copying the last row down however many rows you need to. If you do that, you'll need to redefine the Print Area.