CODEWITHSUNDEEP
excelexcel-formula
Alex Brown
Alex Brown

Reputation: 11

Excel If command to see if a date falls between a date range excluding the year

If I have a date of birth e.g. 1/1/1990, I need to see if this date of birth falls between a date range when when this person stays with me e.g. 12/12/2022 to 02/03/2023. In this example it's Yes.

I've tired this formula but it includes the year

=IF(AND(H4>V4,H4<AA4),"Yes", "No")

where H4 is DOB, V4 is arrival and AA4 is departure.

Any help would be great,
Thanks in advance

Upvotes: 0

Views: 541

Answers (2)

Alex Brown
Alex Brown

Reputation: 11

I approached this slightly differently and it did mean I added a new column to my Spreadheet. This was Next Birthday. The formula is:

=IF(H3<>"",EDATE(H3,(DATEDIF(H3,$I$1,"y")+1)*12),"")

H3 Date of Birth I1 A Preset Date, for me it is 1st Sept of this year,as this is when School Starts

Then the Formula to see if the student would have a birthday when they were with me was:

=IF(AND(AH3>V3,AH3<AA3),"Yes", "No")

AH3 my new column "Next Birthday" V3 Arrival Date AA3 Departure Date

I then simply used some conditional formatting to change those with a Yes to a bold color to be able to see them easily enter image description here

Upvotes: 1

Dominique
Dominique

Reputation: 17493

I don't know the exact functions, but you might opt for this approach:

Take the person't birthday, retrieve day and month and make from that a new date. Do the same with the beginning and ending day and verify if the adapted birthday is between the adapted beginning and ending day.

Edit: example:

Cell C1=DATE(0, MONTH(Birthday),  DAY(Birthday))
Cell C2=DATE(0, MONTH(Beginning), DAY(Beginning))
Cell C3=DATE(0, MONTH(Ending),    DAY(Ending))

Result:

=AND(C3>=C1,C2<=C1)

Upvotes: 1

Related Questions