We have a client that has a complicated vacation policy and I just can't get my brain wrapped around how to write this one out. Any direction would be appreciated.
1) Employees must have been employed at least 1 year before becoming eligible for vacation.
2) On their anniversary date (every year), if they have worked an average of at least 30 hours a pay period for the year, then they receive an accrual equal to that average, with a max of 40 hours. (Formula for that would be total hours for year/52 weeks - does not take into consideration how many checks those hours were received over)
3) If that anniversary date has not occured yet, the most recent anniversary date has to be used (if the anniversary date is 10/01 and the hire year was 2004, then to determine vacation accrual right now, the records from 10/01/2007 to 9/30/2008 must be used to calculate the average hours)
4) Unused hours reset to 0 on the anniversay date.
5) Calculations also must be made going forward from the anniversary date to accrue any potential vacation.
I am assuming that I can do most of this in VBA (we have Excel 2007). I have attached a data sample.
Thanks for any and all suggestions! This is driving me crazy!
LFox
Bookmarks