Excel PTO Accrual

I have searched high and low for excel spreadsheets to match what I am looking for and I have found one. The conversation for that post however has been closed, and so whomever made this spreadsheet I THANK YOU. HOWEVER, I have been racking my brain trying to figure out what I am doing wrong.

PTO Accrues 1 day per 30 hours worked.
However if an employee works 36 hours it doesn't calculate that extra PTO day after the 6's add up.
I also realized my sheet doesn't calculate an extra day if an employee works 60 hours in a week.

I apologize if it is against the rules to use someone else's spreadsheet, and I apologize if I have broken any rules. I have noooo idea what I am doing.

Just need a little help.


Thank you so much!!!!!

Originally Posted by jeffreybrown

Hi debbsuchb & welcome to the forum,

What if you used >> =IF(N(B4),B4/30,"") >> in B4 copied down? This would give you the extra accrual past 30 hours worked.

Make sure you format column B and D1 for 1 decimal place.

Thank you so much! I am going to try that. Can you possibly tell me how you figured that out? I am kind of excel not-so-smart but I would love to learn more. Fast learner here! Generally I can google anything and figure it out but I have so many issues with this 'formula' business.

Originally Posted by jeffreybrown

Since you said every 30 hours you earn 1 day of sick, but you also earn fractional at the 6 hours. So, if you work 36 hours for that week, you have worked 20% more toward another day off of sick.

Another 6 hours, 40%, then 60%, then 80% until you reach 60 hours and then it's 2 days. Since you only showed period of 6 hours over, this seemed to work.

Things might have to be handled differently if you had say, 32 hours, 38 hours, 44 hours.

In the end, if you can give us all the potential scenarios then we can hopefully figure out the right solution.

That makes sense. Unfortunately they work different hours every week sometimes and there is no way to know exactly how many. I guess I could always just calculate it out myself but my company is asking for a 'calculator' which i wouldn't be able to produce because of the odd hours.

Thank you so much for your time!