PTO Accrual Spreadsheet

I know this has been discussed in previous posts, but every PTO policy is different.

I'm putting together a spreadsheet for a start-up client of mine. The would like to see every employee's current PTO balance (approx. 20 people). Employees begin accruing time immediately upon date of hire and earn 4 hours of PTO every pay period (they're paid semi-monthly), for a max of 96 hours per year. I would like to determine their exact amount based on their start date, and how much they've taken.

I can't imagine the formula would be that involved, but would like to have it set up like the attached spreadsheet.

Thanks in advance!

Their situation is a bit different than mine. What I'm looking for is even simpler...accrue up to 96 hours and caps at that. No years of service differentiation. Any help?

This is perfect! Many thanks.

I put "Hours Used" and "Total Used" in case they wanted to use it to record hour taken per pay period, then total hours taken for the duration of their career, or for the year.

You use "360" which I'm assuming is the days?

Why that figure and why not 365 days?

So for "Hours Accrued", do you know what I could use to cap it at 96 hours? I have some employees that are at around 160 hours, and policy only allows max of 96, period.

Thanks, again.

That works well for the balances over 96, but it's changing the ones under 96. For example, on your spreadsheet, the person who started in October and had 4 hrs accrued, now has 20.

This spreadsheet has been extremely helpful. I tweaked it a little an instead of using the "Today" formula to determine length of service, I'm putting in a specific date to determine length from that date. This way, I can go back and get historical PTO accrued amounts.

However, it looks like when from the time between 9/30/11 and 10/31/11, the "Hours Accrued" amount increases by 12 hours, when theoretically it should only be increasing by 8 hours, which it had been prior to that every month since 5/31/11.

I'm having a problem with this formula. Maybe need a whole new one since it's way different that what I need anyway. I need it to calculate as follows.... less than 10 years earns 10.75 hours per month or 16.13 days annually, employees more than 10 years earns 13.33 hours per month and 160 hours annually. Employees can annually carry over a total of 40 hours/5 days. I also need a column to deduct time taken which should not interfere with time accrued.