PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

Attachment 195202Hi Everyone,

I am new to the forum so thank you for any time you have taken out of your day to read and/or assist me.

I've been searching through all the past threads and have done extensive research and I haven't been able to find a formula that will fit my needs so I'm hoping somebody can help. I work for a University so our vacation/sick time accrual is based on the academic year, which is currently July 1, 2012 - June 30, 2013. All of the formulas I have seen work for January to December, however I could be looking at it incorrectly as I am not the most sufficient in Excel.

So basically, I need to create a calculation spreadsheet for the Department that I can forward to other supervisors for their own personal use for their employees based upon the following accrual/allottment rates:

VACATION

Years of Service: Up to 4 years of completed service/ Monthly Vacation Accrual: 1.25 days/ Annual Maximum: 15 days
Years of Service: Over 4 years of completed service and less than 14 years of service/ Monthly Vacation Accrual: 1.67 days/ Annual Maximum: 20 days
Years of Service: Over 14 years of completed service/ Monthly Vacation Accrual: 2 days/ Annual Maximum: 24 days

SICK

Years of Service: Up to 2 years of completed service/ Sick Days Allotted: 22 days/ Days Carried Over to Next Year: None
Years of Service: Over 2 years of completed service and less than 7 years of service/ Sick Days Allotted: 44 days/ Days Carried Over to Next Year: None
Years of Service: Over 7 years of completed service/ Sick Days Allotted: 66 days/ Days Carried Over to Next Year: None
** Employees allowed to use 3 accrued sick days for personal days.

I've included a template where the supervisor can notate what was taken on which day for record keeping purposes (for example, a V on Jul 1), and then enter the number of days taken in each category for the month. I like my template but cannot for the life of me figure out how to enter these formulas. I've worked really hard on this so if there is any kind soul who is willing to help me figure this out, it would be GREATLY appreciated. Please feel free to contact me with any questions. Happy Holidays!