Need Sheet that Calculates/Tracks Staff Time-Off & Paid Time Off Accrual

Hello,

I work for a small non-profit and am trying to set up a spreadsheet that does the following.

- Tracks how much time an employee takes off, either when using PTO or unpaid leave.
- Calculates and tracks how much PTO they currently have, after subtracting what they have used.
- Calculates PTO at the correct rate (based on how long they have been with the org), plus does not add PTO for unpaid leave.

I have been playing with this for a while, but cannot seem to get exactly what I am looking for. I have attached the file I have been working with. One sheet is what I have been trying to create and the other is a sample of what we currently print and keep records on.

If you have any ideas or can think of a better way to record this, please let me know.

Thanks!

-MC

I've believe I've got most of it worked out but there appears to be a units disparity in your calculations and I'm uncertain how to resolve it. Your PTO accrual rate is in days per pay period and it appears PTO accrued is in days, but your PTO taken is in portion of pay period (i.e. PTO days taken ÷ 13). The units for unpaid leave and PTO need to be all the same units across the board...

Thank you for the reply!

Good point - I just went in and changed all of the units to days. The attached sheet has it updated. Will this do it?

I am really curious to hear/see what you have worked out.

Thanks!

See if this (attached file) is what you want. My file has some changes that I made prior to your updated file. Do as please with 'em.

I just put 10 in the unpaid leave column for future days, rather than writing formulas for that.

Jhren,

Thank you, this has given me some more ideas. However, I was not clear in my first description. PTO accrues from the employees first day on the job, they just cannot start to use it until the 3 month mark.

Thanks for all you help! If I can ever get this finalized, I'll post the finished copy back up here.

Originally Posted by mikecann11

Jhren,

Thank you, this has given me some more ideas. However, I was not clear in my first description. PTO accrues from the employees first day on the job, they just cannot start to use it until the 3 month mark.

Thanks for all you help! If I can ever get this finalized, I'll post the finished copy back up here.

Your welcome.

As to the PTO accruing after 3 mos. was my bad (that good ol' ***-u-me factor). It's not hard to edit that out. Just holler if ya need some help...

Hey Jhren,

So, I am almost there but could use a little more help.

1st - FYI I already made this correction. I need PTO to still accrue when an employee is using PTO. I already corrected this.

2nd - Is there a way to have the date range be more specific for the "PTO Earned" calculation. When a person has their anniversary and their PTO accrual rate changes, the rate should change automatically (within the pay period). I see the equation currently uses the first day of the pay period to determine the rate for the whole period, which is normally fine. But, is there a way to have the accrual rate change within the pay period automatically? I have created a sample employee (employee X) on the attached spreadsheet. You can see the rate changes, but not on the specific day.

Any ideas?

Thanks!

Ideas? Yes...

However, I considered that before I made the first day of the pay period as the actual rate-transition date. And the reason was, your sheet does not track which specific days are worked and not worked. When a rate-changing anniversary occurs during a pay period and days were not worked (PTO or leave), there is no way to determine which rate applies to the days that were worked, as the days not worked could have been before or after the anniversary date, or a combination thereof.

I realize the approach I took isn't completely fair, and on the stingy side of being fair. You could take the opposite position and use the last day of the pay period to determine the rate-transition date. Or you could make it proportional regardless of which days were worked and not. Otherwise, you will have to include days worked info to make it completely fair...

I need assistance, I do not know how to calculate vacation time for employees on a spreadsheet- We accrue weekly 40 hours after 1 year of service; 80 after 2 -15 years of service and 120 hours after 15th year. I also want it to deduct when the employee takes time off automatically. We are based on a 52 week pay period. Any help?

Originally Posted by mikecann11

...So, I am almost there but could use a little more help.
...
2nd - Is there a way to have the date range be more specific for the "PTO Earned" calculation. When a person has their anniversary and their PTO accrual rate changes, the rate should change automatically (within the pay period). I see the equation currently uses the first day of the pay period to determine the rate for the whole period, which is normally fine. But, is there a way to have the accrual rate change within the pay period automatically? I have created a sample employee (employee X) on the attached spreadsheet. You can see the rate changes, but not on the specific day.

Any ideas?

Thanks!

Originally Posted by jhren

Ideas? Yes...

... Or you could make it proportional regardless of which days were worked and not. ..

Here's a proportional method...

Originally Posted by [email protected]

I need assistance, I do not know how to calculate vacation time for employees on a spreadsheet- We accrue weekly 40 hours after 1 year of service; 80 after 2 -15 years of service and 120 hours after 15th year. I also want it to deduct when the employee takes time off automatically. We are based on a 52 week pay period. Any help?

You need to start you own thread.

I am looking for a spreadsheet to track PTO/vacation as well however we track it by the number of hours worked. Employees get 1 hour PTO for every 30 hours worked. Is this an option?

I need assistance with creating a formula that will auto populate bi weekly (using a specified PTO amount). The formula will need to add a specific PTO total on every 15th day and last day of the month. It will need to be a live formula that updates every time the excel spreadsheet is open. Some staff members already had PTO that carried over from previous years, so I will need to add that previous time to the current formula. My main goal is to avoid having to enter PTO accrual time every 15th and last day of the month. Could someone please help me out.