Need Formula IF a MONTH & DAY falls in a Specific Week (or between 2 dates)

[Pooger]

This may be a simple formula, but I'm struggling with it. I need a formula that will identify if a Birthday (Month & Day) falls within a specific week (technically between 2 dates in the same week, regardless of year). So, if a month and day of someone's birthday (month & day) falls within the week I'm working with, I'd like to display "Birthday Week".

So if A1 is the birthday, A2 is the week ending date and A3 is the week starting date, what would the formula be to display "BIRTHDAY WEEK" if TRUE and BLANK ("") if FALSE?

A1 01/02/1980 (Birthday)

A2 01/03/2020 (Week Ending Date)

A3 12/29/2019 (Week Starting Date)

Any help would be appreciated! Thank you!

[Pete_UK]

Try this (e.g. in A4):

=IF(AND(DATE(YEAR(A3),MONTH(A1),DAY(A1))<=A2,DATE(YEAR(A3),MONTH(A1),DAY(A1))>=A3),"BIRTHDAY WEEK","")

Hope this helps.

Pete

[Phuocam]

Or try:

=IF(SUMPRODUCT(--(TEXT(A1,"ddmm")=TEXT(A2-{0,1,2,3,4,5,6},"ddmm"))),"BIRTHDAY WEEK","")

[Pooger]

Thank you Pete & Phuocam!!

Phuocam, your formula worked perfectly - THANK YOU!!

Pete, unfortunately, I couldn't get yours to work (not sure why).