Accrual formula

Hello!

I am trying to figure out a formula that will calculate accrued PTO hours based off of total hours worked plus PTO taken for the pay period. There is a catch to this formula. I have the first part figured out. I am stuck when it comes to maximum earned. Example: Employee's PTO balance is 135.4950. The total maximum allowed for this employee is 140.00. They worked 80.00 for pay period and have an accrual rate of 0.10000. If you take 80 times the accrual rate of 0.10000, that equals 8 hours. However, they can only accrue 4.505 because they are almost at their max of 140.00. I hope I explained this clearly. Any help is much appreciated.

Thank you! This works and returns 140. However, I would rather find a formula in the accrued hours column....if possible. Example. The accrued hours columns shows what the employee accrued for the pay period. If the previous pay period had a PTO balance of 135.49 then employee could not accrue the full 8 hours of PTO. They could only accrue 4.505. I would like the accrued hours column to show the 4.505. I hope I am explaining that correctly.

These are my formulas

accrued hours column =E72*$M$4 E72 is the total hours $m$4 is the accrual rate

PTO balance column no max =H71+F72-G72

PTO balance column with max =IF(H72<=140, H72, IF($H72>=140, "140.00"))

I probably made this more complicated than what it needed to be. Trying to simplify

Ok - thanks for helping. This employee's max is 120. I want the accrued column to calculate the hours she can earn without going over the max. Pay date 1/28 the PTO balance is 115.0256. The amount she can earn is 4.9744. The accrual rate column is showing 6.16 because she worked 80 hours and the accrual rate is .065400

Hope this helps. I need the accrued column to calculate what is accrued for the pay period based on the maximum allowed and not what what was accrued for the pay period based on the hours worked * accrual rate.

Example: row 71 column F should display 4.9744 and not 6.1600

Hi! Years of service. I so appreciate you looking at this.

Oh! One more thing. We had a accrual rate change on 11/1/20 for certain departments.