Accrued PTO Spreadsheet Formulas

I'm looking for some help to keep track of accrued PTO hours for our employees. I only have about 15 employees who are eligible for PTO so we are going to be keeping track of this on an excel spreadsheet. Except im horrible with formulas. Our employees are paid weekly, so I have the week ending across the top of the spreadsheet for the entire year. At the end of the spreadsheet I have a column for Total Hours, Total PTO, Total PTO Used and Total PTO available. How our accrual system works is that each week I will be entering in the amount of hours worked for the employee. Then I will multiple the hours worked by .025 which will give me the amount of PTO earned that week.


For example if you worked 37 hours during 1 pay period we will track your accrued PTO by multiplying 37 worked hours by .025 which would equal 0.925. You will not be able to exceed 5 PTO hours in one month. For an example, if you work your max of 40 hours for 5 weeks in one month, it will equal 200 working hours multiplied by .025 which would equal 5 accrued PTO hours.


Can anyone help with some formulas to get me started. Im confused.

Would it be easier to have a tab at the bottom with each employee name and then the week ending dates in the columns? Or should them all on one spreadsheet?