Calculating Monthly PTO Accrual Formula

[Alexwolfey]

Hello,

Our employees receive PTO accrual per month. If they are entitled to 40 hours of PTO per year they accrue at a rate of 3.33 hours per month, and if they are entitled to 80 hours if PTO per year, they accrue at 6.66 hours per month.

I have my spreadsheet so far set up like this as an example:

PTO total hours per year PTO available balance PTO hours used
40 *Want this to auto calculate with formula based on monthly accrual* *When i add hours to this column i want it to subtract from the available balance*

For this example, I would like a formula to calculate automatically in the column "PTO available balance" to accrue at 3.33 hours per month then when I add "PTO hours used" the formula will subtract those hours from the "PTO available balance.

I just cant seem to figure this formula out and any help would be greatly appreciated!

 

[Gimics]

Hey Alex - in order for your accrual to work properly you're going to need to contemplate a rolling total - you'll need some form of "starting" or "opening" amount, and then some sort of date dimensionality. Without providing a date, how would your formula know how much time has elapsed in the year, to calculate the accrual up until?

I would assume that you're just doing it off of today's date (=TODAY()), but you've stated in your requests that this is all performed monthly - so would you want somewhere on the sheet to enter a month and have the calculation updated?

And then is your fiscal/payroll year based on calendar months (Jan - Dec) or something different?


I think it also has to be mentioned that payroll can be complicated and the regulations around things like vacation accruals can vary by tax jurisdiction. You've provided a relatively simple request for support with an Excel formula, but I would caution you to take any answer you get here as the absolutely accurate response as no one on these forums is here to provide you with actual Payroll or Regulatory professional advice. There's a chance that the information you're providing us isn't covering all of your jurisdictional requirements for accrual calculations, even if might seem like it does.

But if you answer the above, someone can totally help you with your request!