20 + 1 evry 365 days but stop at 25

If i start with a number which is 20
and i want to add 1 to it every 365 days,
but stop at 25

Can anyone help with this?

Could you give us more info about it?

1 Like

Im working on a holiday request App for my staff.

Each member starts with a balance of 20 days.
and as each year passes there acrue 1 day extra holiday per year for a maximum of 5 years,
i have the start date for each employee so i want a calculation to add the 1 day automatically but then stops at 25

Instead of a process that adds to a value each year, you really just need to calculate what the current value should be right at this moment.

I would suggest adding a virtual column that performs this calculation. The expression would be something like:

IF (FLOOR(HOUR(TODAY() - [Start Date)/8766.00) >= 5,
   20 + FLOOR(HOUR(TODAY() - [Start Date])/8766.00)

In english, the expression is computing the Total number of HOURS between TODAY and the employee Start Date and then dividing that by the number of hours in a year. This results in a decimal representation of time passed in years. Since you only want whole year based on Start Date, the result is truncated using the FLOOR() function.

The expression checks if the result is greater than or equal to 5 and if so, assigns the value as 25. Otherwise, the number of years is added to the starting value of 20.

You may need to adjust how you actually use the expression depending on your situation.


I agree. Make the expression calculate the value everyday and floor it, with an initial value and a max value assigned

1 Like