20 + 1 evry 365 days but stop at 25

[Simon_Bluck]

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?

[SkrOYC]

Could you give us more info about it?

Guide on How To Create a Community Post Tips & Tricks 💡

Dear valuable members and users, For a long time I’m seeing a lot of multiple and concurrent posts, posts like novels seeming to aim the Pulitzer Price, exactly the same questions by the same user but paraphrased/re-phrased etc. Being a dedicated community member and an AppSheet developer who is reading around 2.5K posts per day, I urged myself to tip on some good advice and best practice BEFORE posting something in the community: #1 - APPSHEET DOCUMENTATION AppSheet has a very good help do…

[Simon_Bluck]

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

[WillowMobileSys]

Simon_Bluck:

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,
   25,
   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.

[SkrOYC]

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