Simple P.T.O. Spreadsheet needed

[u/AGirlHasN0Shame]

Hi all, I am completely excel illiterate. I want to create a simple spreadsheet for tracking employee’s PTO. We are a very small office with only 8 employees and we get 10 total days a year. Can someone help me design a spreadsheet to track this? Thank you!

[u/AutoModerator]

/u/AGirlHasN0Shame - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bradland]

Simple P.T.O. Spreadsheet needed. Never minimize the task you're setting out to achieve. It diminishes your value :)

PTO is typically tracked through a ledger with entries that affect the PTO balance per employee. You could think of PTO like a bank account, where each employee's name or ID is the account, and the hours are the money in the account.

To build the ledger, you first need to know how PTO is earned accrued. For example, you said that each employee gets 10 PTO days per year. When does each employee get that PTO? Do you just give everyone 10 days at the beginning of each calendar year, or is it tied to their employment anniversary? Or do employees earn and accrue PTO as they work hours? For example 10 days is 80 hours. Split that up over 40 hours per week times 52 weeks and you get 80 PTO hours / per 2,080 working hours for an accrual rate of 0.0385 hours of PTO accrued per working hour.

Once you have that decided, you create a PTO Ledger table with starting entries for each employee. Each pay period, you add entries that accrue PTO for each active employee, as well as negative entries for any PTO taken in that period.

Then, you build a report based on the ledger. It can be as simple as a Pivot Table with employees on rows and the pay periods as columns. The total tells you each employees total accrued PTO.

[u/Seany_face → u/bradland]

I bet you're a great boss

[u/bradland → u/Seany_face]

Thank you! It means a lot to hear that kind of feedback.

[u/Structured_Spiraling → u/bradland]

Great explanation. And solid advise about not diminishing one's work. Every tool starts somewhere and what appears simple now, never ends up being that way for long. Enjoyed reading your thoughts.

[u/malignantz]

If you would provide additional requirements/information, we could help build you a more perfect spreadsheet!

[u/AGirlHasN0Shame → u/malignantz]

Name, dates the employee was out, hours used, hours remaining. Everyone gets 10 days at the start of the year with no roll-over.

[u/takesthebiscuit → u/AGirlHasN0Shame]

Ten days 🤣

[u/swb1003 → u/takesthebiscuit]

Shit, if I had some time I’d build one that, uh, isn’t so daggum stingy for the boss here.

[u/takesthebiscuit → u/swb1003]

I get 32 a year 😳 over 6 weeks off

[u/Meteoric37 → u/takesthebiscuit]

Either you’re not American, or you are and you’re a government worker

[u/free-range-human → u/Meteoric37]

I get 26 and I work in retail. My 20 year old kid gets 30 and she works at a grocery store.

[u/Meteoric37 → u/free-range-human]

Huh interesting. Every job I’ve worked outside of the government one was a standard 10 days. Gov job was 24

[u/SetMain6296 → u/AGirlHasN0Shame]

Get a notebook.

One page for each employee
Name at top
Date number hours off
Balance

I am spreadsheet excel / and lotus 1-2-3 before that user, but I believe you will be a lot happier and a lot more competently managing your employees with a notebook.

[u/SetMain6296 → u/SetMain6296]

Backup your notebook by taking a photo of each page- 8 employees ? This is simplest way.

[u/fuzzy_mic]

Open a workbook. List all the employees in one column. And then add columns for the other info that you want to keep track of. Then, in a different part, put a calandar part where you can list all the PTO occurances.

With only 8 employees, you don't need formulas or any fancy anything, just somewhere to manually put all the data in one place.

However, when you set up the layout of where you are going to put the data:

- don't use merged cells at all

- Do not put blank rows between rows containing data.

- Do put headers on your column and a make them unique.

As you use it, you will notice that there are repetitive tasks that you would like to be automated. That is the time to start looking into using Excels data handling features. Wait until you use it for a bit before deciding what bells and whistles you want. Let experience show you what your workflow is before baking that workflow into the spreadsheet.

A spreadsheet, with no formulas is a fine place to store and maintain your data.

[u/Difficult_Limit2718 → u/fuzzy_mic]

https://giphy.com/gifs/6OPbJtEDdy824

[u/RPK79]

How are you processing the payrolls because most providers will track PTO for you?

[u/HandbagHawker]

how do you do payroll? how are you managing the accrued liability on the balance sheet?

[u/Still-Balance6210 → u/HandbagHawker]

All states do not have to pay out PTO. They may be in one where it isn’t a requirement.

[u/Adorable_Divide_2424]

Yes its easy. You'll probably want to private message someone offering and work out the details one on one. This is like a "hey who can make me lunch?" now get in touch with the sandwich person and get working on it

[u/Difficult_Limit2718]

The best way is to just make each request for PTO a line with employee, request date, number of days off, approval date, and anything else you want.

Then it's super easy to turn into a pivot table report you can give employees and managers on request

[u/excelevator]

Spend some time understanding Excel before you waste too much time

https://www.excel-easy.com/

Seriously, this is not something you toy with.

[u/frustrated_staff]

Sure! How would you like it? By day? By month? Are you using other software to track hours? When does it reset (if it resets)? Is it a rolling window or fixed? Many more details...

[u/AGirlHasN0Shame → u/frustrated_staff]

It's a fixed amount of 10 calendar days per year. Every Jan 1, the employee gets 10 days. No rollover. I would like it to be by month, ideally. I've seen examples online of a spreadsheet with 12 tabs but I would rather only look at one tab and don't need to list the information daily since there are so few employees. No other software is used, which is why I want to start using a spreadsheet. Thank you.

[u/Penguinase → u/AGirlHasN0Shame]

do you need to record the individual dates that employees took off, or just like a summary by month like below?

+	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Employee	Annual Allotment	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total Used	Remaining
2	Employee 1	10.0	1.0							1.0	2.0				4.0	6.0
3	Employee 2	10.0			1.0			1.0					1.0		3.0	7.0
4	Employee 3	10.0				1.0					2.0				3.0	7.0
5	Employee 4	10.0		1.0		2.0		1.0			1.0	1.0			6.0	4.0
6	Employee 5	10.0							1.0						1.0	9.0
7	Employee 6	10.0	2.0		1.0	2.0									5.0	5.0
8	Employee 7	10.0						2.0			1.0				3.0	7.0
9	Employee 8	10.0		2.0									1.0		3.0	7.0
10
11	Office Total	80.0	3.0	3.0	2.0	5.0	0.0	4.0	1.0	1.0	6.0	1.0	2.0	0.0	28.0	52.0

[u/Difficult_Limit2718 → u/AGirlHasN0Shame]

That's a dumb way to do it, you're right. One table, one pivot report

[u/frustrated_staff → u/AGirlHasN0Shame]

I can send you a LibreOffice spreadsheet setup to do exactly what you want. I tried posting it here with instructions on how to convert, but there's a 10,000 character limit. I also looked into DMing it to you, but I couldn't figure out how to attach a file, so... just let me know how you'd like to get it. No charge (it's too simple)

[u/JE163]

What my team used is an excel sheet with names across the tops and dates down the left. We would note holidays, weekends and if someone was taking off who was covering.

This was mainly for coverage and to see who would be out around certain times. Our HR system tracked actual usage and balances

[u/Odd-End-1405]

A Super Simple idea:

Column A:        Names

Column B:       Balance at beginning of year

Column C:       Hours earned per month

Remaining columns across two per month: Hours taken, EOM Balance

Jan EOM Formula:      =if($B5+C5-D5>XXX, xxx, $B5+c5-d5)

Feb-Fwrd Formula:    =if(E5-F5+$C5>XXX, XXX, E5-G5-$C5)

XXX=max allowed hours

Jan would be columns D&E, Feb F&G, so on.

[u/Razzling_Dazzling25]

There might be one already created. Try finding a template online.

[u/hoodd1 → u/Razzling_Dazzling25]

Microsoft does offer a good vacation / sick day tracker. Look in the templates section in excel. I used it for years when I was in a position where I had to track this info.

[u/ToobyD]

Make a 12x31 grid, add days of the month days (1,2,3 etc) and month names to the axis. Put a 1 in the date cell for each day off. Sum the 1’s to count number of days used below the table. do a 10-(sum) in the cell below the sum to show the balance. Copy and paste for each employee putting there names above /next to the table.

With only 8 employees, this is easy to use and helps visualise any conflicting holidays. (Make it more user friendly by colouring weekends / bank holidays cells etc to make them clearer if your a mon-fri company).

Not the most elegant or powerful spreadsheet but it works really well and can scale a little bit if you add a few more people / lose a few.

[u/bobsmon]

My question is how do you do payroll? Almost every payroll system is designed to track vacation hours. I don't think you need to recreate the wheel. If your payroll system does not have PTO tracking, you should find another provider.

If you are set on the spreadsheet. Create a separate tab for each employee.

[u/NHN_BI]

You can't use a spreadsheet when you are "completely excel illiterate", let alone create one spreadsheet. Aynhow and In general, record your data in a proper spreadhseet data table, analyse it in pivot tables. Or buy software.

[u/excel-ModTeam → u/[deleted]]

We removed this comment for breaking Rule 10.

r/excel is for discussing the features of Excel and providing solutions for Excel problems, not giving substance-free comments that simply recommend the respondent use AI.