# Date difference to 1st of April IF AND statement

#### mtagliaferri

##### Board Regular
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
Excel Formula:
``=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
Excel Formula:
``=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
Excel Formula:
``=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.

#### Attachments

• Screenshot 2021-02-19 at 23.05.05.png
63.9 KB · Views: 8
• Screenshot 2021-02-19 at 23.05.56.png
25.7 KB · Views: 8
• Screenshot 2021-02-19 at 23.06.28.png
40.8 KB · Views: 8

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### tyija1995

##### Well-known Member
Hey,

something like this?

Book1
ABCDE
1DOJEntitlementAnniversaryTODAY()
215/02/20183001/04/202019/02/2021
315/02/20192801/04/2021
Sheet1
Cell Formulas
RangeFormula
E2E2=TODAY()
B2B2=IF(\$E\$2>DATE(YEAR(A2)+2+(MONTH(A2)>4),4,1),30,28)
B3B3=IF(\$E\$2>=DATE(YEAR(A3)+2+(MONTH(A3)>4),4,1),30,28)
D2:D3D2=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

##### Well-known Member
Hi,

Excel Formula:
``=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
ABCDEFGH
24/1/20184/1/2020230302/19/2021
33/31/20184/1/202023030
44/2/20194/1/202112828
Sheet779
Cell Formulas
RangeFormula
E2:E4E2=IF(AND(YEARFRAC(A2,C2,1)>=2,C2<=H\$2),30,28)
F2:F4F2=IF(AND(\$H\$2>=C2,D2>=2),30,28)
H2H2=MIN(TODAY(),DATE(YEAR(TODAY()),4,1))

Last edited:

#### mtagliaferri

##### Board Regular
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
ABCDEFGH
24/1/20184/1/2020230302/19/2021
33/31/20184/1/202023030
44/2/20194/1/202112828
Sheet779
Cell Formulas
RangeFormula
E2:E4E2=IF(AND(YEARFRAC(A2,C2,1)>=2,C2<=H\$2),30,28)
F2:F4F2=IF(AND(\$H\$2>=C2,D2>=2),30,28)
H2H2=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:
Excel Formula:
``=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
ABCDE
1DOJEntitlement
201/04/201730
331/03/201830
401/04/201830
531/03/201928
601/04/201928
731/03/202028
801/04/202028
901/02/202128
1031/03/202128
1101/04/202128
Sheet3
Cell Formulas
RangeFormula
E2:E11E2=IF(YEARFRAC(A2,MIN(TODAY(),DATE(YEAR(TODAY()),4,1)),1)>=2,30,28)

#### jtakw

##### Well-known Member

You're welcome, looks like you got it

#### mtagliaferri

##### Board Regular
Thanks for the help!!

#### jtakw

##### Well-known Member
You're welcome, happy to help, thanks for the feedback.

Replies
3
Views
157
Replies
0
Views
114
Replies
13
Views
309
Replies
11
Views
454
Replies
1
Views
108

1,127,078
Messages
5,622,553
Members
415,907
Latest member
Walters87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back