• 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 "Like" icon ...just 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 "Like" icon ...just 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 "Like" icon ...just 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.


    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 "Like" icon ...just 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 "Like" icon ...just 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 "Like" icon ...just 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 "Like" icon ...just 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 "Like" icon ...just 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 ?


    Thanks for your feedback

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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 "Like" icon ...just below...in the bottom right corner...:)