Excel Formula to Test if Date is within Date range, ignoring year.

[Maggie Barr]

Greetings and thank you in advance if you can help. I am on a PC using Excel 2019.
I have a formula that someone generously helped me with that I am using to run a test to see if a series of single observation dates in one sheet (Sheet2) for a species falls outside of a given date range in another sheet (Sheet1) for the same species. It provides me different information dependent on which side of the date window it falls on. This is working right now with the years equal across both data sets, but my data for the single date in Sheet2 will soon have data from multiple years, which means the test will no longer work. I only need to know if the month and day fall outside of a specified range, regardless of the year. With the formula I was using (see below), I copied some of the data and changed the year from 2018 to 2020 and what previously was a recorded as “TooEarly” then became “TooLate” because of the year. I need to be able to have the formula check just the month and day for the one record and tell me if it is before after the other month and day, not looking at year.
If anyone has any advice as to how to modify this formula, or if I should try something else, I would greatly appreciate it. I have also loaded a link to a sample workbook below. In that, the blue cells are the made up dates of the identical data (light green cells) and you can see the different results.
I am aware that I could just create a duplicate column and modify all the years to equal 2018 for the purpose of the test, but I am also trying to learn to bring my data into power query because I am reaching Excels record limit (1 million) with much more to come soon, and with my table running as only a connection, it is not adequately replacing values in my duplicated date column with the Power Query editor. I know I will have to modify the language of this formula for power query, but I will cross that bridge when I get there.
Thank you,
Maggie Barr
Link to file:
https://app.box.com/s/szfp43a1vbf7w5v6poh9w50pyp65c08b
Formula:
'=IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!C:C,"<="&B2,Sheet1!D:D,">="&B2)=0,IF(B2>INDEX(Sheet1!D:D,MATCH(A2,Sheet1!A:A,0)),"TooLate","TooEarly"),"DateOkay"),"Sp; Code N/A")

 

[Chrisdontm]

I have a formula to try...
Put this formula in cell: C2 on Sheet2, then copy it on down.

What I did was; when referencing B2, I used the Day of B2 and the Month of B2 and the Year from Sheet 1 all within the formula.

See if this will get you down the road...

=IF(ISNUMBER(MATCH($A2,Sheet1!$A:$A,0)),IF(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$C:$C,"<="&DATE(YEAR(Sheet1!$C$2),MONTH($B2),DAY($B2)),Sheet1!$D:$D,">="&DATE(YEAR(Sheet1!$C$2),MONTH($B2),DAY($B2)))=0,IF(DATE(YEAR(Sheet1!$C$2),MONTH($B2),DAY($B2))>INDEX(Sheet1!$D:$D,MATCH($A2,Sheet1!$A:$A,0)),"TooLate","TooEarly"),"DateOkay"),"Sp; Code N/A")

 

[Maggie Barr]

Chrisdontm,
Thank you so very much for posting this, it is AMAZING. I have read through the formula multiple times, and even with your instructions, I have yet to decipher exactly what part is making this work without consideration of year, I will keep working at it. When you said "Day of B2 and the Month of B2 and the Year from Sheet 1 all within the formula." I still do not see how the year being different in sheet one from the year in sheet two works, but it works. I have ordered the Excel 2019 Bible and M is for (Data) Monkey, so perhaps with those books in hand I will be better able to decipher the formula and be able to learn how to modify it accordingly for use within the Power Query mode for the larger data set. I just can't thank you enough for your assistance. It is truly appreciated! I have had multiple occasion where assistance through this forum has helped me immensely. I am always so appreciative of the amazing resource being offered here and the kind and helpful people who make it such.
With deep gratitude,
Best Wishes,
Maggie Barr

 

[mikerickson]

If there are dates in A1 and B1, then

=TEXT(A1,"mm/dd") < TEXT(B1,"mm/dd")

Will return if the date in A1 is before the date in B1, ignoring years.

 

[Maggie Barr]

Mike Rickson,
Thank you for posting that, the simplified version at the root of that enables me to see that. I was just not understanding how the year is still in formula and the language associated "YEAR(Sheet1!$C$2") I think is where the difference is, but I could be wrong. I have a long way to go in my understanding of the Excel language, but, again, I am always so grateful for everyone's help.
Best,
Maggie