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!

 

[Brose8]

You're welcome, thanks for the feedback.

As I was doing this for all employees, I realised it does not take into account half days - so for example - Employee started on 4th Nov 2019, so they are entitled to 24.5 days as they started after June 30th, but calculation puts it as 24 days. I'm not sure if this can be taken into account in the same formula.

 

[Georgiboy]

Does that mean you need to capture the difference in years as 3.5 for example?

Might be easier to come up with some kind of lookup table, something like the below, i will be able to work with that:

Book1
	A	B
1	Years Service	Holiday
2	0	20
3	1	20
4	2	22
5	2.5	23
6	3	24
7	3.5	24.5
8	4	25
9	etc.	etc.
Sheet1

 

[Brose8]

Does that mean you need to capture the difference in years as 3.5 for example?

Might be easier to come up with some kind of lookup table, something like the below, i will be able to work with that:

Book1
	A	B
1	Years Service	Holiday
2	0	20
3	1	20
4	2	22
5	2.5	23
6	3	24
7	3.5	24.5
8	4	25
9	etc.	etc.
Sheet1

Yes, this looks about right. The full entitlement is allowed from Jan 1 - June 30 for start dates. The half entitlement is Jul 1 -Dec 3 for start dates.

 

[Georgiboy]

The below works purely from their start date, if from their start date to today is 3.5 years then you will get the value from the table that is next to 3.5. It is not looking at specific months for the start date:

Book1
	A	B	C	D	E	F	G
1	Name	Start Date	New			Years Service	Holiday
2	Person 1	02/08/1987	30			0	20
3	Person 2	03/05/1999	30			1	20
4	Person 3	01/01/2001	30			2	22
5	Person 4	01/01/2017	27.5			3	24
6	Person 5	01/01/2020	24.5			3.5	24.5
7	Person 6	01/11/2020	22			4	25
8	Person 7	01/01/2023	20			4.5	25.5
9						5	26
10						5.5	26.5
11						6	27
12						6.5	27.5
13						7	28
14						7.5	28.5
15						8	29
16						8.5	29.5
17						9	30
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),F2:F17,G2:G17,,-1))))
Dynamic array formulas.

 

[Brose8]

The below works purely from their start date, if from their start date to today is 3.5 years then you will get the value from the table that is next to 3.5. It is not looking at specific months for the start date:

Book1
	A	B	C	D	E	F	G
1	Name	Start Date	New			Years Service	Holiday
2	Person 1	02/08/1987	30			0	20
3	Person 2	03/05/1999	30			1	20
4	Person 3	01/01/2001	30			2	22
5	Person 4	01/01/2017	27.5			3	24
6	Person 5	01/01/2020	24.5			3.5	24.5
7	Person 6	01/11/2020	22			4	25
8	Person 7	01/01/2023	20			4.5	25.5
9						5	26
10						5.5	26.5
11						6	27
12						6.5	27.5
13						7	28
14						7.5	28.5
15						8	29
16						8.5	29.5
17						9	30
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),F2:F17,G2:G17,,-1))))
Dynamic array formulas.

Sorry to be dumb - What is the 'holiday' vs 'new' column you're using? Thanks

 

[Georgiboy]

I just named the column New so you would see that it is a new formula.

 

[Brose8]

I just named the column New so you would see that it is a new formula.

Is there an easy formula to calculate the years of service column as well so this is automated based on the inputted start date and have it include half as well? i.e. 5.5 years

I was using =INT(YEARFRAC(C7,TODAY())), but this does not make some of them at 5.5 years, etc.

 

[Georgiboy]

Like this:

b.xlsx
	A	B	C	D	E	F	G	H
1	Name	Start Date	Years service	New			Years Service	Holiday
2	Person 1	02/08/1987	36.2	30			0	20
3	Person 2	03/05/1999	24.4	30			1	20
4	Person 3	01/01/2001	22.8	30			2	22
5	Person 4	01/01/2017	6.8	27.5			3	24
6	Person 5	01/01/2020	3.8	24.5			3.5	24.5
7	Person 6	01/11/2020	2.9	22			4	25
8	Person 7	01/01/2023	0.8	20			4.5	25.5
9							5	26
10							5.5	26.5
11							6	27
12							6.5	27.5
13							7	28
14							7.5	28.5
15							8	29
16							8.5	29.5
17							9	30
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=ROUND(DATEDIF(B2:B8,TODAY(),"m")/12,1)
D2:D8	D2	=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),G2:G17,H2:H17,,-1))))
Dynamic array formulas.

 

[Brose8]

Like this:

b.xlsx
	A	B	C	D	E	F	G	H
1	Name	Start Date	Years service	New			Years Service	Holiday
2	Person 1	02/08/1987	36.2	30			0	20
3	Person 2	03/05/1999	24.4	30			1	20
4	Person 3	01/01/2001	22.8	30			2	22
5	Person 4	01/01/2017	6.8	27.5			3	24
6	Person 5	01/01/2020	3.8	24.5			3.5	24.5
7	Person 6	01/11/2020	2.9	22			4	25
8	Person 7	01/01/2023	0.8	20			4.5	25.5
9							5	26
10							5.5	26.5
11							6	27
12							6.5	27.5
13							7	28
14							7.5	28.5
15							8	29
16							8.5	29.5
17							9	30
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=ROUND(DATEDIF(B2:B8,TODAY(),"m")/12,1)
D2:D8	D2	=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),G2:G17,H2:H17,,-1))))
Dynamic array formulas.

Thank you so much for all the help!

 

[Georgiboy]

You are welcome