# PTO Accrual

• I'm trying to figure out some formulas to calculate how much PTO my employees have, and here are the rules for our PTO
PTO is figured by an hourly rate
PTO can be rolled over at the max amount of PTO available for the year
Once an employee has reached their max amount of PTO and taken all of their PTO for the year, they begin accruing for next year
Once an employee reaches their max amount of PTO, PTO stops accruing until they use some
An employee cannot use more than their allotted PTO for the year
I have a VLookup to figure out max PTO hours possible, and their accrual rate.
I enter their daily time, and that adds up to figure out how many PTO hours that they have earned

So, what I'm trying to calculate is total PTO hours, how many are available for this year, and how many they have earned towards their rollover

This is what I have, but the PTO Hours available for 2019 are not calculating correctly (Date of hire begins on row 8 and the first employee begins in Column C)
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 159, colspan: 2"]Date of Hire[/TD]
[TD="width: 94, colspan: 2"]4/11/1989[/TD]
[TD="width: 90, colspan: 2"]11/1/2011[/TD]
[TD="width: 90, colspan: 2"]4/1/1987[/TD]
[TD="width: 94, colspan: 2"]3/11/1996[/TD]
[TD="width: 90, colspan: 2"]9/9/1971[/TD]
[TD="width: 90, colspan: 2"]6/10/2019[/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]Service Length[/TD]
[TD="width: 94, colspan: 2"]30[/TD]
[TD="width: 90, colspan: 2"]8[/TD]
[TD="width: 90, colspan: 2"]32[/TD]
[TD="width: 94, colspan: 2"]23[/TD]
[TD="width: 90, colspan: 2"]48[/TD]
[TD="width: 90, colspan: 2"]1[/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]Union[/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]Salaried[/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"]x[/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]

[/tr]

[tr]

[TD="width: 86"] [/TD]
[TD="width: 73"] [/TD]
[TD="width: 94, colspan: 2"]A Plant[/TD]
[TD="width: 90, colspan: 2"]Sales[/TD]
[TD="width: 90, colspan: 2"]Shop[/TD]
[TD="width: 94, colspan: 2"]A Plant[/TD]
[TD="width: 90, colspan: 2"]Geode[/TD]
[TD="width: 90, colspan: 2"]A Plant[/TD]

[/tr]

[tr]

[TD="colspan: 2"]Classification[/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] Office / Mgr [/TD]
[TD="colspan: 2"] Office / Mgr [/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] Field [/TD]

[/tr]

[tr]

[td][/td]

[td][/td]

[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]Yearly Totals[/TD]
[TD="colspan: 2"] 990.50[/TD]
[TD="colspan: 2"] 1,136.00[/TD]
[TD="colspan: 2"] 1,139.50[/TD]
[TD="colspan: 2"] 1,445.25[/TD]
[TD="colspan: 2"] 1,025.75[/TD]
[TD="colspan: 2"] 211.00[/TD]

[/tr]

[tr]

[TD="colspan: 2"]PTO Hrs Possible[/TD]
[TD="colspan: 2"]120[/TD]
[TD="colspan: 2"]160[/TD]
[TD="colspan: 2"]160[/TD]
[TD="colspan: 2"]120[/TD]
[TD="colspan: 2"]120[/TD]
[TD="colspan: 2"]56[/TD]

[/tr]

[tr]

[TD="colspan: 2"]Accrual Rate[/TD]
[TD="colspan: 2"]0.075[/TD]
[TD="colspan: 2"]0.1[/TD]
[TD="colspan: 2"]0.1[/TD]
[TD="colspan: 2"]0.075[/TD]
[TD="colspan: 2"]0.075[/TD]
[TD="colspan: 2"]0.03[/TD]

[/tr]

[tr]

[TD="colspan: 2"]PTO Hrs Earned[/TD]
[TD="colspan: 2"] 74.29[/TD]
[TD="colspan: 2"] 113.60[/TD]
[TD="colspan: 2"] 113.95[/TD]
[TD="colspan: 2"] 108.39[/TD]
[TD="colspan: 2"] 76.93[/TD]
[TD="colspan: 2"] 6.33[/TD]

[/tr]

[tr]

[TD="colspan: 2"]PTO Hrs Carried Over[/TD]
[TD="colspan: 2"]4.33[/TD]
[TD="colspan: 2"]94.4[/TD]
[TD="colspan: 2"]55.02[/TD]
[TD="colspan: 2"]30.13[/TD]
[TD="colspan: 2"]115.99[/TD]
[TD="colspan: 2"]0[/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]PTO Hrs Used[/TD]
[TD="colspan: 2"] 32.00[/TD]
[TD="colspan: 2"] 48.00[/TD]
[TD="colspan: 2"] 34.00[/TD]
[TD="colspan: 2"] 34.00[/TD]
[TD="colspan: 2"] 112.50[/TD]
[TD="colspan: 2"] - [/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]PTO Hrs Scheduled[/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]PTO Hrs available for 2019[/TD]
[TD="colspan: 2"] 78.62[/TD]
[TD="colspan: 2"] 112.00[/TD]
[TD="colspan: 2"] 126.00[/TD]
[TD="colspan: 2"] 86.00[/TD]
[TD="colspan: 2"] 7.50[/TD]
[TD="colspan: 2"] 6.33[/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]Total PTO Hrs Available[/TD]
[TD="colspan: 2"] 46.62[/TD]
[TD="colspan: 2"] 160.00[/TD]
[TD="colspan: 2"] 134.97[/TD]
[TD="colspan: 2"] 104.52[/TD]
[TD="colspan: 2"] 80.42[/TD]
[TD="colspan: 2"] 6.33[/TD]

[/tr]

[tr]

[TD="width: 159, colspan: 2"]PTO Hrs Earned for Rollover[/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] 48.00[/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] 18.52[/TD]
[TD="colspan: 2"] 72.92[/TD]
[TD="colspan: 2"] - [/TD]

[/tr]

[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 319, colspan: 2"]PTO Hrs available for 2019[/TD]
[TD="width: 525, colspan: 2"]=IF(OR(C18>=C16,C18+C19>=C16),C16-C20,C18+C19)[/TD]

[/tr]

[tr]

[TD="width: 319, colspan: 2"]Total PTO Hrs Available[/TD]
[TD="colspan: 2"]=MIN(C16,IF(SUM(C18,C19,-C20)=C16,C16,SUM(C18,C19,-C20)))[/TD]

[/tr]

[tr]

[TD="width: 319, colspan: 2"]PTO Hrs Earned for Rollover[/TD]
[TD="colspan: 2"]=IF(OR(C16-C20<=0,C20+C18-C16<=0),0,(C23-C22))[/TD]

[/tr]

[/TABLE]

Thanks in advance for any help.

• What is the difference between "PTO Hrs available for 2019", and "Total PTO Hours Available"? Is one calculated to account for "PTO Hrs Scheduled", and one isn't? And, you're saying that Rollover PTO is only generated if they accrue the max amount of PTO allotted and then use it all? After which, the accrued PTO would then go toward next year? So if someone is only allowed 150 hours of PTO and they have 150 hours, and they use 20, they have 130 left. Then they wait a while before they take the next vacation, so they now saved 150 hours total again. Are they only allowed to use 130 hours of that PTO and that extra 20 hours is just saved for next year? I'm not exactly sure if I explained that correctly, but that is what is seems like to me. Would you be able to attach the spreadsheet? This would make it easier to analyze everything. Happy to help, though. This is fun for me.

• Hi,

Do not know is PTO stands for Police Training Officer or for Pick To Order .. :lol::lol::lol:

In Both cases .... Attaching a sample file would make things ... SO MUCH easier ...!!! :rock:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Just noticed PianoLady posted her message two months ago ...

and never came back to the Forum ...since ...

:facepull:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• PTO stands for Paid Time Off. PTO is accrued towards next year even if an employee has reached their max PTO for this year, but if they don't take PTO during the year, their PTO stops accruing until they use some. So, I have a max PTO of 120 hours. Last year, I only used 88 hours and I earned a total of 126.26 hours, so I rolled over 38.26. I can only use 120 hours of PTO in a year, even if I don't take all of last years PTO, but anything accrued over 120 hours rolls over to next year. It's a really confusing PTO. I hope this answered your questions. I have made some changes to the formulas and I think I might have it now, which is why I hadn't logged in for a while, but I'd like to see your thoughts anyway. I am unable to upload this spreadsheet because it has confidential employee information.

Here are the adjusted formulas that I have.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 319, colspan: 2"]PTO Hrs available for 2019[/TD]
[TD="width: 1257, colspan: 2"]=IF(OR(C18>=C16,C18+C19>=C16),C16-C20,C18+C19-C20)[/TD]

[/tr]

[tr]

[TD="width: 319, colspan: 2"]Total PTO Hrs Available[/TD]
[TD="colspan: 2"]=MIN(C16,IF(SUM(C18,C19,-C20)=C16,C16,SUM(C18,C19,-C20)))[/TD]

[/tr]

[tr]

[TD="width: 319, colspan: 2"]PTO Hrs Earned for Rollover[/TD]
[TD="colspan: 2"]=IF(OR(C16-C20<=0,C20+C19+C18-C16<=0),0,(C23-C22))[/TD]

[/tr]

[/TABLE]

• Hello,

Thanks a lot for your clarification :smile:

A possibility would be to make a copy of our file ... and then remove all the Confidential Data ...while keeping the formulas ... and then attach to your next message this newly ' anonymized ' file ... :wink:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Sorry for the delay in getting this posted. I had a long vacation and I was busy up until I left and today is my first day back. I removed the confidential information and some of the employee columns, so this file wouldn't be quite so large.

## Files

• Sorry for the delay in getting this posted. I had a long vacation and I was busy up until I left and today is my first day back. I removed the confidential information and some of the employee columns, so this file wouldn't be quite so large.

Good to hear you could test PTO ...:lol:

Thanks for your test file ...

Will take a look at your yearly formulas ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Hello again,

Merged Cells should be TOTALLY BANNED ...:thumbdow::thumbdow::thumbdow:

They can be the cause of a huge number of problems ... !!!

How do you come up with the results shown in Row 11 ...?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• I use the merged cells to separate hours worked (the first column for each employee) and hours of PTO taken (2nd column for each employee). I'm open to other ideas, but I couldn't get the results I needed when we switched from regular vacation to PTO.

I received the information on row 11 from a report that my corporate HR dept sent out.

• Thanks for the clarifications ...

So what are precisely the rows and formulas you need to be re-assured about ... ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• I'm having trouble with the calculations in rows 14, 15, & 16. One problem I have is that there is some sort of rounding issue between what my payroll dept uses and how excel calculates, but I can work around that.

Row 14 should be how many hours that they have available for the year, but it cannot go over their PTO Hrs Possible shown in row 8. This includes their rollover, but only until they reach the PTO Hrs Possible for the year. I hope that makes sense.

Row 15 should be the total PTO hours available, which includes anything that they carried over, and they continue to accumulate PTO even after they've reached their max.

Row 16 is what they should be rolling over, but I don't want to see a negative quantity if they are still accumulated hours for this year.

• Thanks

Will try to translate into formulas ... all your specific constraints ... :wink:

Should I need your assistance ... will get back to you ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Just realized I have completely forgotten to take a look at your file ...:yikes:

Very very sorry for that ...!!! :?

Will take the time over the week-end ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• As promised, attached is your revised test file Version 3

Could you review the formulas in C14:AJ16 range ...

Is there any cell which does not display your expected result ?

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Hello,

Have you had a chance to take a look at the PTO range ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Yes, I took a look at it. Thanks for you help!

• You are welcome

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Carim,

I would like to get rid of the merged cells in this spreadsheet. I have a VBA formula to sum Italics (SumItalics) and I'd like to combine it with the formulas in rows 12 & 13. Do you have any ideas on that?

• Hi,

Have you made modifications to your sheet structure ... or is the latest V3 a good reflection of your current workbook ...?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!