Annual Leave Entitlement forumla

[Brose8]

Hello!
I am trying to automate entitlement every year based on my employer.

Starting leave is 20 days.
Following 2 yrs of employment - increase from 20 to 22 days
Following 3 yrs of employment - increase from 22 to 24 days.
Each year following - increase by 1 day up to a max of 30 total annual leave days.

Entitlement runs Jan- Dec.
Based on when contract started- if start date was Jan 1- June 30 you receive full allocation.
If start date was July 1-Dec 31, you receive 50% of the increase.

Many thanks for your help!

 

[Georgiboy]

Welcome to the forum, was you after something like the below:

Book1
	A	B	C
1	Name	Start Date	Entitlement
2	Person 1	02/08/1987	30
3	Person 2	03/05/1999	30
4	Person 3	01/01/2001	30
5	Person 4	01/01/2017	27
6	Person 5	01/01/2020	24
7	Person 6	01/11/2020	22
8	Person 7	01/01/2023	20
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"y"), IFS(dd<2,20,dd=2,22,dd=3,24,dd>3,MIN(24+dd-3,30)))))
Dynamic array formulas.

 

[Brose8]

Welcome to the forum, was you after something like the below:

Book1
	A	B	C
1	Name	Start Date	Entitlement
2	Person 1	02/08/1987	30
3	Person 2	03/05/1999	30
4	Person 3	01/01/2001	30
5	Person 4	01/01/2017	27
6	Person 5	01/01/2020	24
7	Person 6	01/11/2020	22
8	Person 7	01/01/2023	20
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"y"), IFS(dd<2,20,dd=2,22,dd=3,24,dd>3,MIN(24+dd-3,30)))))
Dynamic array formulas.

Hi! For some reason, that formula is not working for me. It says it is missing an opening or closing parenthesis. I did change the cell #s to match mine, but I am new to these larger formulas, so maybe I need to fill out something else within it differently?

 

[Georgiboy]

You should be able to just change the B2:B8 part.

Are your dates arranged virtically?

It could also be that your excel needs to have all of the , to be replaced with ; (If your usual seperator in formula is

 

[Brose8]

You should be able to just change the B2:B8 part.

Are your dates arranged virtically?

It could also be that your excel needs to have all of the , to be replaced with ; (If your usual seperator in formula is

Hi! Thank you - yes the dates are arranged vertically. I tried again and am now getting a #SPILL error. I also tried replacing all , with ; and get the formula error pop up.

 

[Georgiboy]

With the #SPILL error it means that you have data under the formula where the formula is trying to spill into. Try clearing the cells under the formula you put in place that produced the #SPILL error.

 

[Brose8]

You should be able to just change the B2:B8 part.

Are your dates arranged virtically?

It could also be that your excel needs to have all of the , to be replaced with ; (If your usual seperator in formula is

I deleted everything out of the cells to be sure and this now works Further question - will the current entitlement automatically change dependent on the current date when you pull up the spreadsheet? Not sure how that piece works. I am trying to do this for a 2024 future spreadsheet, so obviously the entitlement goes up for next year.

 

[Georgiboy]

Excellent, yes this part of the formula: TODAY() means it is always working with today as the date, it will calculate the difference in years between their start date and today and then calculate from that result.

 

[Brose8]

Excellent, yes this part of the formula: TODAY() means it is always working with today as the date, it will calculate the difference in years between their start date and today and then calculate from that result.

Wonderful! Thank you so much for clarification on the original post

 

[Georgiboy]

You're welcome, thanks for the feedback.