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!