Calculate Date + 2 years starting 1st Jan

[monkeyharris]

Having a mental block. I'm setting up a staff holiday calendar and need to calculate when staff are awarded extra holiday based on service.
So, an employee gets an extra day after 2 years, problem is, if they joined in July 2022, they won't get the additional holiday until the January of 2025, so the formula needs to calculate 05/07/22 to today, and if more than 2 years, automatically say 1, after 4 years it's 2, 6 years is 3, 8 years is 4 and 10 years is 5 days extra.

A1 shows their joining date. Example 05/07/22 so their addition day will appear in 2025 calendar.

 

[etaf]

you could use datediff
=int(DATEDIF(A1,TODAY(),"Y")/2)
to calculate the entailments as of now
but how does 1 jan fit in ???
maybe with a min

min(int(DATEDIF(A1,TODAY(),"Y")/2),5)

Book1
	A	B	C
1	5-Jul-04		5
2	6-Jul-06		5
3	6-Jul-08		5
4	7-Jul-10		5
5	7-Jul-12		5
6	8-Jul-14		5
7	8-Jul-16		4
8	9-Jul-18		3
9	9-Jul-20		2
10	10-Jul-22		1
11	10-Jul-24		0
Sheet1

Cell Formulas
Range	Formula
C1:C11	C1	=MIN(INT(DATEDIF(A1,TODAY(),"Y")/2),5)

 

[monkeyharris]

you could use datediff
=int(DATEDIF(A1,TODAY(),"Y")/2)
to calculate the entailments as of now
but how does 1 jan fit in ???
maybe with a min

min(int(DATEDIF(A1,TODAY(),"Y")/2),5)

Book1
	A	B	C
1	5-Jul-04		5
2	6-Jul-06		5
3	6-Jul-08		5
4	7-Jul-10		5
5	7-Jul-12		5
6	8-Jul-14		5
7	8-Jul-16		4
8	9-Jul-18		3
9	9-Jul-20		2
10	10-Jul-22		1
11	10-Jul-24		0
Sheet1

Cell Formulas
Range	Formula
C1:C11	C1	=MIN(INT(DATEDIF(A1,TODAY(),"Y")/2),5)

Thanks Etaf.
The 1st Jan comes into it because the holiday year runs from 1st Jan to 31st Dec. If you join midway through the year, the additional date earnt doesn't count until the next holiday year.

 

[etaf]

so the calc can be carried anytime in excel - just wont apply till the 1st jan, in which case rather than today - should be 31/12/24
not sure how you want to include that info

not quite sure of the process you will use it for
i assume at omepoint you just feed into a 2025 holiday entiltemt

so maybe the endate should be 31/dec/24

=MIN(INT(DATEDIF(A1,DATEVALUE("31/12/2024"),"Y")/2),5)

Book1
	A	B	C
1	5-Jul-04		5
2	6-Jul-06		5
3	6-Jul-08		5
4	7-Jul-10		5
5	7-Jul-12		5
6	8-Jul-14		5
7	8-Jul-16		4
8	9-Jul-18		3
9	9-Jul-20		2
10	10-Jul-22		1
11	31-Dec-22		1
12	1/1/23		0
Sheet1

Cell Formulas
Range	Formula
C1:C12	C1	=MIN(INT(DATEDIF(A1,DATEVALUE("31/12/2024"),"Y")/2),5)

 

[kvsrinivasamurthy]

Try.
A2=Entry date, Calculation for today's date.
In B2

=INT(DATEDIF(DATE(YEAR(A2)+(((DAY(A2)>1)+(MONTH(A2)>1))>0),1,1),TODAY(),"Y")/2)

 

[FormR]

Hi, here's another option you could try.

Book1
	A	B
1	Start Date	Extra Days
2	5-Jul-2004	5
3	6-Jul-2006	5
4	6-Jul-2008	5
5	7-Jul-2010	5
6	7-Jul-2012	5
7	8-Jul-2014	4
8	8-Jul-2016	3
9	9-Jul-2018	2
10	9-Jul-2020	1
11	10-Jul-2022	0
12	10-Jul-2024	0
13	1-Jan-2022	1
14	2-Jan-2022	0
15	1-Jan-2020	2
16	2-Jan-2020	1
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=MATCH(YEAR(TODAY())-YEAR(A2-1)-1,{-1,2,4,6,8,10},1)-1