I've been trying to create a sharepoint list that displays the current available balance of an employee's vacation hours.
I'm having issues creating a column that is calculated based on several other columns that have rules and parameters.
My company's time off policy is:
1st year employees, pro rata up to 40 hours accrued throughout the year, then on the anniversary date, a gift of 40 hours is given automatically. After 3 years service, 80 hours per year is given on the anniversary date, and then after 7 years of service, 120 hours is given on each anniversary date. Hours are carried over up to a max of 120 hours.
The issue I'm having is trying to track the current running total of hours based on length of service AND how many hours have been used, but then add the designated amount to their total on each anniversary date.
I've tried several methods, but always end up calculating the column into a dead end.