Time Off Tracker Template

If you need to track employee sick days and vacation, then the time off tracker template can help you. This template can also help you decide whether to approve or deny time-off requests as well. Below, I’ll go over the main features of the template and how it works.

Setting up the time off tracker

On the Summary tab, you’ll start with a list of all the employees you want to track. This will include their hourly wage, start date, their beginning balance for their vacation days, as well as their vacation rate. The time off tracker template will also track sick days as well.

You can enter the current year and the cutoff date if you want to see up to a certain point in time. The annual hours and vacation rate percentage will impact the annual vacation day accrual. Annual hours you’ll probably want to set to either 2,000 (50 weeks x 40 hours) or 2,080 (52 weeks x 40 hours).

For sick days, there is a section off to the right on the summary page where you can enter the number of sick days people are entitled to annually. You can also specify a maximum number of people that can be off at any one time. This is related to approving requests which I’ll cover further down.

You’ll also see a section for holidays and blackout days for when you don’t want people taking time off. These lists can be as long as you like.

Entering and requesting time off

Whether you want to check if a person can book time off during a certain period or if you want to actually book it, you’ll do this through the Request.Form tab. Here, you can select the employee, the type of request (vacation or sick), and how long they will be off for. This template assumes employees do not work weekends. If the request includes a weekend, it will automatically account for that. Here’s a sample request for someone looking to take Jan. 24 – Jan. 31 off.

The available days, hours, days requested and hours requested will all automatically fill in once you enter all your selections. You’ll notice the days requested equals six, which isn’t counting Saturday and Sunday. It also assumes these are full days off and hence multiplies the days off by an eight-hour workday to get to 48 hours requested off.

If you want to see if this request complies with your policy (blackout rules, maximum people off) you can click on the Check Availability button. Then you will see the following summary:

The lights come up green for having sufficient time available and there being enough coverage. But it comes up red because the request includes a blackout day. Over on the right-hand side, you’ll see the person’s most recent time-off requests. It will also show any people who are off during this time.

You can still proceed and click the Post Time-Off Request button either way. It will post the information into the Timeoff tab:

If I double-click on any of the red boxes to the right, it will delete an entry. There is no data that needs to be entered on this tab, this is simply for record-keeping. The Timeoff tab is used to populate other areas of the spreadsheet.

If I were to go back and try and book another entry on Jan. 31 for a different employee, it will now tell me that someone is off during this time:

Note: you won’t be prevented from posting vacation if a person already has vacation booked for the same date. But if you make a mistake you can clear the duplicate entry from the Timeoff tab. You can also visually see who is off during a given period on the Calendar tab:

Blackout days are highlighted in black, vacation is dark blue, weekends are light blue and sick days are in yellow. In the above example it shows the one employee who took a vacation day during the blackout period. The color code for vacation overrides the blackout formatting. From this, you can also see that two employees were off on Jan. 31.

Entering in partial-day requests

If an employee is taking a half-day or only a certain amount of hours off rather than a full day, you can select the Partial Day option from the drop-down in the Request.Form tab. A new field will appear, allowing you to enter the hours per day:

In this example, this employee is requesting 5 hours off on both Jan. 30 and Jan. 31. The employee is requesting a total of 10 hours off, or 1.25 days. If you need to do a partial request, you can’t mix and match with full-day requests, you’ll need to do them separately. Even if the hours are different, multiple requests will be needed.

Whether an employee takes a full or a partial day off, it’ll look the same on the Calendar tab.

Adjusting for rate changes

One of the more complex calculations this time off tracker template factors in is if an employee moves to new pay rate or if their vacation rate changes during the year. This is where the Ratechanges tab comes into play. You select the employee, the date that the change is effective as well as the date it ends – this can be left blank if there’s no further rate change. The purpose of the end date is if there are multiple changes for an employee during the year.

Then, you enter the new hourly wage and vacation rate. Enter both numbers. For instance, if an employee now accrues 6% vacation rather than 4%, you’ll want to enter the new vacation rate but you’ll also want to enter in their hourly wage as well, even if it remains the same. This is important so that the formula calculates correctly.

This calculation will spit out a change in daily accrual as well as a total adjustment for the year based on the cut-off date. This adjustment will populate on the Summary tab under the Vacation Days Adj (Rate Changes) field.

As with any complex calculations, always be sure to double-check these numbers against your own. Especially when it comes to multiple rate changes a year, it’s important to ensure the data is entered correctly and that the correct number of days are being accrued. Although I’ve tested the spreadsheet, it’s impossible to factor in every possible situation and so I cannot guarantee 100% accuracy in all situations.

If you’d like to test out the template, you can download the free version here which is limited to tracking five employees. The full version has no limits and there are no ads and the code is fully unlocked.

If you liked this post on the time off tracker template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Vacation Accrued excel table

Accrued Vacation and Time Off Template

***Please note there is a newer version of this template that is available here

Tracking employee vacation and time-off requests can be a bit of a headache. This template will help track time off as well as how much vacation has been used up and how much is remaining. It also helps to prepare your recurring journal entries to expense vacation.

Download Options

Free version: Download Here

Full version: (no ads/workbook locks): see product page

About This Template

This template will help you track and reconcile vacation liability owed to employees. Both in hours accrued and taken, and also in dollar values if you need to as well.

Vacation Accrual – Employee Data

The first tab is the EmployeeInfo tab which is where you will need to enter your employee information. All the cells in light blue are ones where you can enter information. The dark blue cells have formulas and are locked. In the below example I’ve filled in some sample employee information:

Vacation Accrued excel table

You can replace the data in the table and you can also add rows by just typing the employee name in the next blank row in column A and the table will automatically re-size.

If you do not want to track dollars accrued then you can simply leave the wage field blank. This template will allow you to track vacation dollars and hours even if there are wage or vacation rate changes. In the above example, Jack Smith had a wage of $10 from January 1 until May 31. And from June 1st his wage changed to $15 and vacation rate increased from 4% to 6%. I’ll go over this calculation later in the post to show you that it has calculated properly in the reconciliation tab.

If there are no rate changes you can leave the end date field blank. You only need to use the end date field if the employee has a change in vacation days or wages, or is no longer with the company. In the latter case you will also want to set their status to Inactive. Doing so will exclude them from the JE tab and calculation.

Also on this tab is a section to enter the holidays for the year, and this is to ensure that the vacation calculation ignores these non-working days. The next section is to ensure the calculation is done correctly and the right percentages are used – daily hours will likely stay as 8 and the annual hours to used in the vacation rate calculation. So if you are paying an employee 4% vacation that is equivalent to 10 vacation days based on 2,000 hours.

The cutoff date is up to what date you want to reconcile to. So if you wanted to know what the vacation liability looks like at the end of the year you would set it to Dec 31, as I have below.

Holidays Table Employee Time Off Tracker

The next tab is the TimeOff tab. This is where you can select when the employee took vacation, and I added an option for sick as well in case you wanted to track that.

Employee Time Off In the above example, John Burns is away January 4th, 5th, and 6th, so I enter the 4th as the start date, and the 6th as the end date. The next business day would be the day he is back at work. The hours taken reflects the average daily hours from the previous tab. The wage information also comes from that tab. On the Calendar tab you will see a year-long calendar off the time off. This isn’t part of the vacation calculation but allows you to visually track vacation and sick time taken. Time Off and Vacation Calendar You see above that the days for January 4, 5, and 6 are highlighted for John Burns to indicate he took vacation on these days. Sick days will show in a different colour. Weekends and holidays are also filled in with light blue. This would be more of a scheduling tool when you are dealing with many employees and wanted to minimize time off conflicts and overlaps.  Accrued Vacation ReconciliationThe Reconciliation tab shows a summary of all of the time earned and vacation time taken off Accrued Vacation and Time Off Reconciliation Table

You can enter a starting date (this does not affect any calculations), a starting balance if you carry forward vacation from the previous year, and adjustments for any items not captured in the hours earned or time off taken (e.g. vacation pay out). The other cells are formulas and are locked.

I will refer to my example above where Jack Smith had a change in wage and vacation rate. His wage won’t affect this calculation but his vacation rate will.

From January 1 to May 31 he will accrue at a rate of 4%. Since he works 8 hours a day x 104 working days which fall within this range once holidays are factored in, that is a total of 832 working hours. 4% of this total is 33.28 vacation hours.

The second range is from June 1 until the end of the year. During this range there are 146 working days x 8 hours for a total of 1,168 total hours worked. At his new rate of 6% this is 70.08.

If I total 33.28 and 70.08 that gives me 103.36 hours earned which is what the reconciliation shows.

If you track dollars there is a separate section for the dollars accrued which works in the same way

Accrued Vacation Dollars Reconciliation Table

Again going back to my example earlier, Jack Smith had a $10 wage from January 1 to May 31st at a 4% vacation rate. If I multiply 104 working days by his daily pay (daily hours of 8 x wage of $10 = $80 per day) then I arrive at 104 x 80 = $8,320. And 4% of this is $332.80

Now from June 1st to Dec 31st that is 146 working days x his new daily rate of $120 ($15 wage x 8 daily hours) is a total wage of 17,520. 6% of 17,520 is $1,051.20.

If I add the two amounts together, I get $1,384 (1,051.20 + 332.80). As you see this equals the dollars earned for Jack Smith.

Vacation Accrual Journal Entry

The last tab is the JE tab which is used to generate your vacation accrual journal entry.

Accrued Vacation Journal Entry

The light blue fields are ones you can enter. You will need to specify the GL accounts, pay period, start, and end dates. There is also a space where you can put an adjustment for an employee. For instance, your accrual might be for a two week period but if someone was terminated or shouldn’t have accrued for any period of time here is where you can make the adjustment.

The employees listed as active will show up in this list as well as their wage during the dates you enter – as long as you don’t have rate changes that occur in the middle of a pay period there should be no issues here.

Please note this spreadsheet should be used to help reconcile to your payroll provider’s data to ensure accuracy and completeness. Do not use this spreadsheet alone in determining your vacation liabilities as I do not offer any guarantees as to its accuracy.