We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
If you’re growing a small business, you’re probably all about saving money where you can. As you begin to hire employees, you may wonder if you can do employee time tracking in Excel to track your employees’ hours worked. Good news! Take a look below at a way that you can track employee hours worked and calculate their PTO in Excel. Also, see how your company may benefit from using employee time tracking software.
Create an Employee Time Tracker in Excel
The Scenario:
In this example, we’ll build an employee time tracker sheet for an hourly employee. This is a full-time employee who gets 2 weeks of vacation each year. She can earn more or less than that depending on the number of hours that she works.
There are many ways you can track employee time and calculate PTO. Here, we’ll build a time tracking sheet to help you track your employee’s hours worked in a week and calculate their PTO. Make sure to adjust for your specific situation.
Create a section to quickly see the key details for your employee
Dedicating a section of your spreadsheet to the employee’s key details can help you quickly see their information.
Ideally, you want to create this near the top of your sheet. I like it on the top left corner.
This section should include key employee information, such as their name, ID, etc.
It should also include data about the employee’s full-time or part-time status and vacation or PTO allotment for the year.
How to Calculate PTO Earned Hourly
You can now use this data to help you calculate the PTO the employee will earn per hour worked.
The formula will be:
Base Vacation Benefit per Year / (Hours per Week * Work Weeks in Year)
In this example, this would be:
80/(40*52) = 0.0385
Create the Column Headers and Set the Formatting
Date Column
The Date Column will reflect the date worked. It should be formatted as a date.
For example, 12/30/2019
Day Column
This column displays the day of the week that corresponds with the date. It’s not necessary, but I find it helpful.
This field can reference the date field but be formatted to display the day of the week.
To apply this custom formatting, select your cell and:
- Right-click
- Select Format Cells
- Under Category, select Custom
- Type dddd in the Type field
- Click OK
Time Clocked In and Time Clocked Out Columns
In these columns, you will log the time the employee clocked in and then clocked out for the day.
These should be formatted with a time format, ideally one that will display AM or PM.
Breaks Column
In this column, you can log the time the employee spent on break. For example, 1:00 for lunch.
If you want to log the time the employee clocked in and out for breaks, you can copy the Clocked In and Clocked Out columns and relabel them for Breaks.
This column should use a date format, but not display AM or PM.
Hours Worked Column
This column will be used to calculate the total hours the employee worked.
The formula used here will be:
(Time Clocked Out – Breaks – Time Clocked In) * 24
Multiplying the resulting time value by 24 will convert it to a regular number.
This should be formatted using Number formatting.
PTO Hours Earned (or Vacation Hours Earned) Column
This column will display the amount of PTO or vacation time that the associate earned per hour worked for the day.
The formula here will be:
Hours Worked * PTO Hourly
*Consider locking the reference to the PTO Hourly value (using the F4 keyboard shortcut) so that the cell reference doesn’t change when you copy and paste this formula to other cells.
This column should be formatted as a Number, but with increased decimal values.
PTO (or Vacation) Used Column
This column will display any hours the employee took for vacation or PTO. It should be formatted as a Number.
PTO (or Vacation) Remaining Column
The PTO or Vacation time remaining column will display the vacation time the employee has for that day.
In this example, the employee can use vacation time before they have earned it for the year, so the total can be negative.
The formula used here will be:
PTO Earned – PTO Used
This column should be formatted with Number formatting and increased decimals.
Once you have created your columns and formatted the first day, you can drag this down to create the first week of your time tracker template.
Convert your data to a Table
While it’s not necessary to convert your Employee Time Tracker into a Table, I find that doing so makes it easier to use.
Converting it to a Table automatically creates references to the selected columns if you add new formulas.
The many style options for formatting the Table can also help your time tracker instantly look more professional.
To convert your employee time tracker into a Table, place your cursor anywhere on the table and either press the Ctrl+T keyboard shortcut or click on your preferred Table layout from the Home tab / Styles section / Format as Table
Calculate Weekly Hours Totals
Once you’ve converted your data to a Table, it’ll be a good idea to add a row to total the results for the week.
To do this, go to the last cell of your table and Tab over to add another row.
Delete anything that may have auto-populated.
To add the totals for the week, place your cell under the Hours Worked for the week. Use the Alt+= keyboard shortcut to sum the hours.
Copy this formula to also sum the total PTO Earned and any PTO Used for the week.
Then, to calculate the PTO Remaining total for this first week, subtract the total PTO Used from the total PTO Earned.
Create and Format Subsequent Weeks
The first step to creating the subsequent weeks for your time tracking spreadsheet is to format the second week.
To make the second week, copy the first week, including the week totals, and paste them just below your Table. When you paste the data, it will automatically be added to the existing Table.
Next, format the Date field so that it displays the correct date. To do this, create a formula on the Monday of the second week to reference the last day of the prior week and add 1.
Then, go to the Tuesday of the second week, reference the Monday and add 1 to that. Then drag down the cells in the Date field for the remainder of the week so that it copies over your new formula.
Lastly, update the formula in the PTO Remaining week total to also include the total from the week prior.
Now, your subsequent weeks should all copy down correctly!
Create an Employee PTO Balance (or Vacation Balance) Summary
It’s a good idea to create an employee PTO balance summary – ideally near the top of the page where you have the other employee key details.
This will serve as a tool to help you see the current PTO status of your employee.
For this example, we’ll use 3 fields:
1. Calculate Year to Date PTO Earned
To calculate the current PTO Earned, use a formula to calculate the total in the PTO Earned column of your tracker and divide by 2.
*Dividing by 2 is important because, otherwise, the weekly totals will be included, doubling your result.
2. Calculate Year to Date PTO Used
To calculate the current PTO Used year to date, use a formula to calculate the total in the PTO Used column of your tracker and divide by 2.
3. Calculate PTO Available
To calculate the PTO Available, subtract the PTO Used Year to Date from the PTO Earned Year to Date.
Final Thoughts on Tracking
Employee Hours and Vacation / PTO
Making an employee PTO & Time Tracker can be a lot of work – not just to create, but also to update and maintain.
A super easy option you can use to help you keep track of your employees’ hours and PTO balance is the cloud-based time tracking service from On the Clock.
Their time clock system automatically calculates employee hours worked while generating accurate time cards for payroll. It can be easily accessed from desktops, tablets and smartphone devices. This includes a mobile app option where employees can conveniently clock in and out if their employer allows it.
Plus, their app makes it easy for both you and your employees to see their PTO balance in real-time.
*Click for your FREE 30-day trial (without using a credit card!)*
And, if your small business is just starting to take off, they offer their service for free if you have 2 employees or less.
Sign up for their 30-day free trial (without using a credit card!).
FYI, this was created using the desktop version of Excel in Microsoft 365.
Leave a Reply