Date difference to 1st of April IF AND statement

[mtagliaferri]

I am trying to determine leave entitlement based on the length of service by the 1st of April of the year and if current date (Today()) is greater than or equal to 1st of April.
The employee starts with 28 days of leave which then become 30 once completed 2 full years of service by the 1st of April.
For the below example I am replacing Today() with Current Date
Date of Joining 15/02/18 current date 19/02/21 entitlement 30 from 01/0420 (as by that date has 2+ years of service.
Date of Joining 15/02/19 current date 19/02/21 entitlement 28 (although is 2+ years of service it is not yet 1st of April) however once current date is 01/04/21 or over it will become 30 days.

I have attached a few screenshots of my very wingy way of getting to what I want and still is not working.

The approach I have taken is to determine when is the 2 year anniversary from date of joining using

=DATE(YEAR(A2)+2+(MONTH(A2)>4),4,1)

I have then calculated the date difference in years between Date of Joining and Anniversary using

=DATEDIF(A2,C2,"y")

Then I would get the entitlement by using an IF AND statement which would determine IF Current Date (which would be Today() on the final project) is => than Anniversary date AND Years => 2 would return 30 otherwise 28 using

=IF(AND("$H$2">="C2",D3>=2,),28,30)

Unfortunately I am struggling with the formula, I have also split the IF AND formula to see if it worked in Case 1 and 2 but still have hit a brick wall.
I am sure there is a less wingy way of getting the solution in one single formula.

 

[tyija1995]

Hey,

something like this?

Book1
	A	B	C	D	E
1	DOJ	Entitlement		Anniversary	TODAY()
2	15/02/2018	30		01/04/2020	19/02/2021
3	15/02/2019	28		01/04/2021
Sheet1

Cell Formulas
Range	Formula
E2	E2	=TODAY()
B2	B2	=IF($E$2>DATE(YEAR(A2)+2+(MONTH(A2)>4),4,1),30,28)
B3	B3	=IF($E$2>=DATE(YEAR(A3)+2+(MONTH(A3)>4),4,1),30,28)
D2:D3	D2	=DATE(YEAR(A2)+2+(MONTH(A2)>4),4,1)

Col B will list how many days of entitlement is required based on today() function

 

[jtakw]

Hi,

=IF(AND("$H$2">="C2",D3>=2,),28,30)

Unfortunately I am struggling with the formula, I have also split the IF AND formula to see if it worked in Case 1 and 2 but still have hit a brick wall.
I am sure there is a less wingy way of getting the solution in one single formula.

First of all, noticed your formula cell reference(s) are inconsistent, and the quote marks do not belong, it probably should be =IF(AND($H$2>=C2,D2>=2),30,28)

Below is my suggestion in E2 copied down (if used, D Column will no longer be needed), F2 is your formula corrected, H2 will show the Lesser of Today or April 1 of the current year:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	DOJ		Anniversary		Entitlement	Your formula corrected		Today
2	4/1/2018		4/1/2020	2	30	30		2/19/2021
3	3/31/2018		4/1/2020	2	30	30
4	4/2/2019		4/1/2021	1	28	28
Sheet779

Cell Formulas
Range	Formula
E2:E4	E2	=IF(AND(YEARFRAC(A2,C2,1)>=2,C2<=H$2),30,28)
F2:F4	F2	=IF(AND($H$2>=C2,D2>=2),30,28)
H2	H2	=MIN(TODAY(),DATE(YEAR(TODAY()),4,1))

 

[mtagliaferri]

Hi,



First of all, noticed your formula cell reference(s) are inconsistent, and the quote marks do not belong, it probably should be =IF(AND($H$2>=C2,D2>=2),30,28)

Below is my suggestion in E2 copied down (if used, D Column will no longer be needed), F2 is your formula corrected, H2 will show the Lesser of Today or April 1 of the current year:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	DOJ		Anniversary		Entitlement	Your formula corrected		Today
2	4/1/2018		4/1/2020	2	30	30		2/19/2021
3	3/31/2018		4/1/2020	2	30	30
4	4/2/2019		4/1/2021	1	28	28
Sheet779

Cell Formulas
Range	Formula
E2:E4	E2	=IF(AND(YEARFRAC(A2,C2,1)>=2,C2<=H$2),30,28)
F2:F4	F2	=IF(AND($H$2>=C2,D2>=2),30,28)
H2	H2	=MIN(TODAY(),DATE(YEAR(TODAY()),4,1))

Hi, Thank you for the explanation, I believe I am getting there!!

I realised in my example that the Anniversary calculation was not reliable and replaced it with your formula, Ialso tried to simplify all by using only the DOJ column and the revised formula as below:

=IF(YEARFRAC(A2,MIN(TODAY(),DATE(YEAR(TODAY()),4,1)),1)>=2,30,28)

I have done some testing and I think I am getting the result I need but just want to verify as not sure the Today() is going to impact the outcome... the entitlement will be 30 for everyone that has 2 or more years as of the 1st of April of the current year?

Book1.xlsx
	A	B	C	D	E
1	DOJ				Entitlement
2	01/04/2017				30
3	31/03/2018				30
4	01/04/2018				30
5	31/03/2019				28
6	01/04/2019				28
7	31/03/2020				28
8	01/04/2020				28
9	01/02/2021				28
10	31/03/2021				28
11	01/04/2021				28
Sheet3

Cell Formulas
Range	Formula
E2:E11	E2	=IF(YEARFRAC(A2,MIN(TODAY(),DATE(YEAR(TODAY()),4,1)),1)>=2,30,28)

 

[jtakw]

You're welcome, looks like you got it

 

[mtagliaferri]

Thanks for the help!!

 

[jtakw]

You're welcome, happy to help, thanks for the feedback.