Vacation Accrual

[kanolan]

Hi everyone,
Thanks to all of you, I’ve made great progress in my vacation accrual spreadsheet, but have some new problems I hope you can help me out with because I’m at a total loss as to how to even start. I’ve attached my spreadsheet so you can take a look and hopefully give me some ideas.

The first problem is that employees change their accrual rate based on their hire date, but vacation is accrued on a calendar year (Jan-Dec). For example, emp. # 6, Matthew Doe’s hire date was 3/4/08. Based on that, he would accrue vacation at the 1 week rate (0.769) from Jan 1, 2009 through 3/3/09 and would accrue at the 2 week rate (1.539) from 3/4/09 through 12/31/09. How would I write formulas in the hours earned and # of hours columns for these situations?

Along that same line, how would I write the formulas for new hires like emp. #18, Betty Doe who should only start accruing vacation as of her start date, but it calculates her as accruing since January 1?

Also, we have several part-time folks who work anywhere from 4 to 6.5 hours per day 2, 3 or 4 days per week (that also may or may not change their accrual rate through the year). They would earn the same # of weeks based upon the hours they work – say emp. #17, Greg Doe only works 3 days per week, 5 hours per day. He would be accruing 2 weeks of vacation time, which would be 15 hours per week for a total of 30 hours, but he’s also changing to the 3 week accrual schedule as of 5/10/09.

I’m not very well versed in formula writing and the last two columns of the spreadsheet calculate time used and time remaining. The formulas I wrote are very basic (a2+c2+e2 etc) and I was wondering if there is a better way to do this that would be more accurate?

Sorry for the long post, but I really wanted to make sure that I explained this as best I could. I’m at a complete loss as to how to even start this and any help at all would be much appreciated! Thanks everyone!