Help with Formula to Keep Running Total of Accrued Vacation and Sick Time

Hello,

I have started a spreadsheet (attached) which will be for tracking sick and vacation time used and keep running totals for each as well. The spreadsheet should be useable by everyone in the business so I want the formulas to refer to cells L7-L9 and print the running totals in cells C2 & C4.

Sick Hours accrue at 4.6 hours per pay period
Vacation Hours accrue as follows: 0-7 years service = 3.1 hr/pay, 8-14yrs = 4.6hr/pay, 15-24yrs = 6.2hr/pay, and 25+yrs = 7.7hr/pay

This spreadsheet needs to know what the date is currently, and add the appropriate amount of sick and vacation hours accrued and subtract any hours used in each category.

To further clarify, if I started the year with 737.3hrs sick time and it is today (4/3/12), my sick hour balance printed in cell C4 would be 737.3hr + (4.6 hrs accrued per period * 6 periods) - 80 sick hours used thus far = 684.9 hours

If you can offer me help with the formulas I would greatly appreciate it. Thank you for taking the time to help.

Best Regards,

Joe

Hello and thank you for your quick response.

I would like C2 and C4 to contain the formulas and print the results of the current hour balances.

Therefore, cell C2 for vacation balance would be: C2=L8-SUM(F9:F35)+(4.6 hours * 6 pay periods), but I want the spreadsheet to track what the date is currently so it knows how many pay periods of accrued hours to add. The balances in C2 and C4 would then reflect the current hours of sick and vacation time.

Does this answer your question?

- Joe

I made a couple of errors that I just realized. We do not start with a vacation balance but we do start with a sick hours balance. Therefore, cell L9 does not need to be included but cell L8 does.

The pay period is actually 7 right now because it is 4/3/12. The spreadsheet formulas need to encompass the current date. So if it is 4/3, we are in pay period 8 according to the range in columns C and D, and have accrued 7 pay periods worth of hours. So if it is 7/5/12, we have accrued 13 pay periods of hours.

I apologize if this is confusing and sorry for the errors.