Paid time off (PTO) and sick leave are excellent perks that help you hire top talent and keep employee morale up.
But as an employer, it’s your responsibility to track employee leave benefits throughout the year. You use employee timesheets (or time tracking software) to log work hours. But you also need a solution to help you track how much vacation and sick time each worker has used.
One option is a template. Here is our vacation and sick time tracking spreadsheet template. Just click "Make a copy" to start editing your own!
We’ll get into how to use the template soon. But first, let’s look at why you need to track employees’ vacation and sick time.
Why Do You Need to Track Employee Vacation Days and Sick Time?
Many employers offer a set number of vacation and sick days per year. Most companies provide an average of 10 vacation days per year outside of paid holidays (like Christmas) and sick days.
Let’s say your PTO plan has a maximum number of days off. In that case, it’s important to track vacation day accrual and employee attendance. That way, your team members aren’t taking off more time than they’re given (even if it’s accidental).
After all, you’re still paying employees for this time. You don’t want to pay for more hours than you defined in your plan.
When you monitor employee leave, it also becomes easy for your employees to see how much vacation or sick time they have left, so they don’t have to track it themselves or guess how many days off they have remaining.
Hourly payroll software makes it easy to record sick leave and PTO from your phone, so employees can always see how much time they have in the bank—and so can managers.
But What If Your Company Offers Unlimited PTO?
Even then, using an attendance tracker can help with leave management. Tracking hours off provides insight into how employees are using your benefits. You can also review your time tracking to see if vacation time affects performance.
You can even identify employees who haven’t used their leave yet and suggest they take a vacation or use personal days to avoid burnout.
Who Should Keep Track of Employee Attendance?
In many companies, attendance tracking and leave management are the responsibility of the HR department. Human resources employees can also create approval forms for vacation requests. These are worksheets that employees fill out to get their vacation time approved.
However, small businesses and startups don’t always have a dedicated HR team. In these cases, business owners or team managers may be responsible for tracking employee hours and creating a leave request form.
So, how do you go about tracking employee attendance? Let’s take a look at how to use an existing template and make your own tracking spreadsheets.
How Do I Keep Track of Employee Sick Days and Vacation Time?
As an employer, you can use spreadsheets or time tracking software like Hourly to log how much time off each person has used.
For instance, you can track employee vacation and sick days in Excel or Google Sheets with a vacation and sick time tracking spreadsheet template like one of these:
- Employee vacation and leave tracker [Google Sheets template]
- Employee vacation and leave tracker [Microsoft Excel template]
The above time tracking templates let you record two types of leave: vacation hours and sick leave.
Here’s how to use them.
1. Make a Copy of the Template
Select Make a copy when prompted to save the time tracker template to your computer or company’s Google Drive.
2. Add Employee Names on the Total Tab
Go to the first tab titled Total and add employee names. Names should go in the cells with the text “Last Name, First Name.” Once you update a name on the Total tab, you should see it automatically appear on each of the month tabs.
Add employees alphabetically by their last names to make it easy to navigate your spreadsheet.
3. Add the [Year] and [Supervisor] on All Pages
In the cells that say “[Year],” replace that text with the year you’re tracking. You can add a manager’s name in the cells that say “[Supervisor]” if you like. This is helpful for companies that have several teams with their own managers who track and approve leave.
4. Enter the Total Number of Vacation and Sick Days Allotted
You’ll see a column on the Total tab with the title “Total Allotted.” This is where you enter the total number of annual leave hours that each person has.
This spreadsheet uses hours to make it easier to track partial days off. One full day of work is equal to eight hours. So, if your employee vacation plan includes a total of 10 vacation days, enter “80” under “Total Allotted” for vacation hours.
These numbers will be the same for each employee if everyone at your company receives the same amount of time off each year.
But, if employees have different amounts of time off allowed, you can customize this spreadsheet to reflect that. This typically happens if you give employees more vacation time after they’ve worked with you for a few years.
5. Record Vacation Hours and Sick Hours for Each Month
Next, you’ll see that each month has its own tab. This is where you’ll enter usage based on the day and whether they used vacation hours or sick time.
We’ll use January as an example. Let’s say an employee goes on vacation on Jan. 10 and 11. You want to record eight hours (one full day off) on each of those days.
So, find the January tab and 10 column (Jan. 10) and enter “8” in that employee’s Vacation hours used row. Do the same thing for the 11 column.
After you’ve updated these two cells, you’ll see that the Total column automatically adds up the time off and displays “16.”
To record sick time, follow the same process except using the row labeled “Sick time hours used.”
Let’s say your second employee takes four hours (a half day) of sick leave off on the 20th. Under the 20 column, you want to record “4.” Once again, you’ll see that the Total amount for that row automatically updates.
6. Review Page 1 ‘Total’ to See the Remaining Time Off
After you’ve recorded vacation time and sick hours, you’ll see that the Total tab automatically records the hours used for each month. In other words, you only have to record the number of hours taken off per day.
The spreadsheet automatically calculates monthly and yearly totals for hours used and hours remaining.
You can check the Total tab at any point to see how many hours each employee has taken off. It’s also helpful for showing how much vacation and sick time each worker has left.
How Do I Make a PTO Spreadsheet?
If you can’t find a template that you like, you can always make your own vacation tracker spreadsheet. At the very least, it should include:
- Employee’s name
- Total number of days (or hours) off allowed
- Total number of days (or hours) off used
- Total number of days (or hours) off remaining
Using the basic information, create a spreadsheet with the following columns:
With this format, each line is one employee’s vacation time. Here you can input the total number of vacation days they’re allowed per year and update the sheet every time they use one.
To automatically calculate the remaining days off, you can use a formula to subtract “Total Days Used” from “Total Days Allowed.”
How Do I Make a Sick Time Spreadsheet?
You can follow the same format for tracking sick days in a spreadsheet template. In this case, you’ll change the column names to “sick time” instead of “PTO.”
The columns should look something like this:
In “Total Sick Days Allowed,” input the maximum number of sick days the employee can take. Then, update the number in the “Total Sick Days Used” column each time they use a sick day.
Finally, you can use a formula to automatically calculate “Total Sick Days Remaining.” The formula should subtract the number in “Total Sick Days Used” from the number in “Total Sick Days Allowed.”
Spreadsheets vs. Software: What’s Better for Leave Management?
Spreadsheets can help you streamline your employee hour tracking, and they’re helpful if you prefer a more manual process. However, manual tracking can take up more time and may include more human data entry errors.
But what if you already use software for time tracking? In that case, it may be easier to use the software to record vacation and sick leave hours, too.
Many time trackers offer desktop and mobile apps that let employees view their remaining time off and submit requests all in one place. With Hourly, you can easily record sick leave and PTO as well as streamline payroll, manage employee documents, and automatically withhold payroll taxes.
Now that you know the available options, all that’s left to do is to pick the one that works best for you and your team.
Ready to transform your business into a profit-pumping machine? Learn how with our monthly newsletter.
