In this Excel tutorial,
we're going to create a time-tracking sheet
you can use to track employee hours worked,
and their vacation time, or PTO, for that hourly employee.
We'll then look at an option that you can use
to help make time tracking easier
for both you and your employees.
We'll be using one sheet per employee.
So we'll start by labeling this sheet
with the employee's name.
First, we'll create a section where you can, at a glance,
quickly view the key details
and PTO status for your employee.
In addition, the values that we'll use here
will also help us in calculating the employee's PTO earned
per hour that they've worked.
So in this example, Sasha's base vacation benefit
is 80 hours, or two weeks, per year.
She can earn more or less than that
based on the number of hours she works.
We'll use 52 for the number of work weeks.
And FYI, the values that we're using here
may be different for your business.
She's a full-time employee, so we'll use 40 hours per week.
And now we can use these values to calculate
how much vacation time she'll earn for each hour worked.
So to do this, multiply the 40 hours by 52.
Then we'll need to divide this result
by the 80 hours we're basing her vacation time off of.
So in this example, Sasha will earn 0.0385 vacation hours
per hour that she works.
Next, let's make the headers for the time tracker.
We'll use Date, Day, Time Clocked In,
Time Clocked Out, Breaks, Hours Worked,
Vacation Time, or PTO Hours Earned,
PTO Used, and PTO Remaining.
So we'll start by typing the date.
The first Monday of 2020 was actually December 30th, 2019.
Then the Day field will be the day of the week.
So we'll just reference the Date field,
but we'll apply some custom formatting
so that it shows the day of the week that it is.
So the next few fields, we'll use time formatting,
and, so that our total calculates correctly,
the formatting we apply here is going to be important.
So for the Clocked In and Out fields,
we'll apply a time format with an a.m. or p.m. designation.
And I'm gonna select the one that says 1:30 p.m.
And we can type in 8:00 a.m.
And we'll say Sasha clocked out on the 30th at 6:00 p.m.
The Breaks column also needs to be formatted as time.
But this time we'll use a format
that does not display an a.m. or a p.m.
The reason for this is that we're showing
the amount of time on break, not the time
that the associate clocked in and out for break.
So if you want to show the time
that they clocked in and out,
you can copy over the Clocked In and Clocked Out columns
and just label them accordingly.
And then when we enter the amount of time
the associate was on break,
we'll still enter one hour using the colon
as if it was in hour format.
Then, to calculate the Hours Worked,
we'll subtract the breaks and the time clocked in
from the time the associate clocked out.
We'll then multiply this by 24
to convert the time value to a regular number.
And you may need to update the formatting
back to number format from time.
So we can use this to calculate the PTO Earned.
And the formula for this will be
the total hours worked times the PTO Hourly.
And we will lock this
so that we can use this in other cells.
PTO Used will remain blank
until the associate uses some PTO or vacation time.
And PTO Remaining, we'll subtract PTO Used from PTO Earned.
And now select these fields and drag them down
to complete a full week.
And I've created this for a seven day workweek,
Monday through Sunday.
Your business may only need this Monday through Friday.
So, next, we're going to delete any fields
that need to be entered later.
And then, with your active cell anywhere on this data,
we'll convert this to a Table.
And coming to the last cell of the Table,
we'll tab over to add another row,
and delete anything that may have auto populated.
Under Hours Worked, we'll add the number of hours worked,
and bold that.
And we'll copy this over to PTO Earned and PTO Used.
And we'll format PTO Earned
to have a few more decimal points.
Now, PTO Remaining will calculate the PTO Used
from any PTO Earned.
And we'll bold this as well.
So this is our first week.
To create the second week, I'm simply going to copy this.
And, directly beneath the Table, I'm going to paste it.
And it will still be a part of this Table.
Now what I'm going to do different here is,
on the Monday of the second week,
I'm going to insert a formula to reference
the last day of the prior week, and add one.
Then the cell below that, that next Tuesday,
we'll reference the Monday above it,
and will add one to that as well.
And then we'll just drag this down.
We'll delete here where it says Saturday,
and we'll revise this PTO formula
to still be PTO Used for that week, from the PTO Earned,
but we'll also add the remaining PTO from the week prior.
And let's actually copy these clocked in and out times down,
so that we have some data to look at.
And let's say on Saturday,
Sasha worked from 9:00 a.m. to noon,
and she didn't take a break.
And we'll say that this week, she took four hours vacation.
So we'll say she left at 12:00 p.m. on Thursday,
and didn't have a lunch.
So now that we have this second week
with our revised date formula
and the revised PTO Remaining formula,
we can copy the second week, delete that Saturday,
and now all the subsequent weeks that we copy down
should calculate correctly.
Then over here, so we can see her current PTO status,
we'll create a formula for PTO Earned Year to Date,
where we will sum the PTO Earned column
and divide that by two.
And the reason we're dividing by two
is because we're technically adding everything twice,
where we have the PTO by day,
and then again, that same total for the week.
And we'll do the same for the PTO Used,
and divide that by two.
And then PTO Available Year to Date,
we'll subtract the PTO Used from any PTO that's been earned.
So this can be complex.
And with all the manual entering and copying and pasting,
there's a lot of room for error.
Not to mention collecting accurately
all the hours from your staff.
So an option that I've been impressed with
is the cloud-based time tracking service from OnTheClock.
Their app, whether on mobile or desktop,
makes it easy for your employees to clock in and out.
And you can both view regular hours worked
and PTO balance real time.
So check them out.
I'll include a link in the description.
They offer a free 30 day trial.
And if you have two employees or less, it's free!
So thank you, OnTheClock, for sponsoring this tutorial.
And to see more videos like this, click on these links.
Thanks.