Sharepoint list for vacation accruals and usage

Question

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.

Answer 1

As you want to add hours to the column according to the service time, it is beyond the reach of a simple calculated column. The formula in it does not have the feature to fetch the data of current time or other service years.

If you have the column recording the years of service, say "service year" column. A calculated column can get the time of hours with a formula like 3*[service year] that is all it can do.

If you have access to Microsoft Power Automate(Used to be called Flow), it will be possible to construct a flow to be triggered on a certain time and update the column value. A custom workflow will do the work too with some code work in Visual studio.