Range by Date (Not Including Year)

[Noz2k]

What I am trying to do is call back all values where the date is between 2 user inputted dates irrespective of the year. This is in order to show anniversary dates, so the year still needs to be returned, but not included in the criteria.

 

[GlennUK]

Call back by using what Excel command? is it by using Data/Filter/Advanced Filter?

 

[Noz2k]

No, it's inside an Index Match function

 

[GlennUK]

And must this be done via formulas and not Advanced Filter? If so, can you have an extra column in the data to test against the dates ( irrespective of year ) to give a TRUE/FALSE result, and test that in your formula?

 

[Noz2k]

It does need to be in formula.

That could possibly work I think. However I'm not sure what the formula for that would be.

How do I say If dd/mm from cell1 is between dd/mm from cell2 and cell3 then return true if not return false.

The Left function doesn't work with dates does it?

 

[Jonmo1]

Try

=AND(TEXT(cell1,"dd/mm")+0>=TEXT(cell2,"dd/mm")+0,TEXT(cell1,"dd/mm")+0<=TEXT(cell3,"dd/mm")+0)

 

[Noz2k]

This is the formula at the moment, which works for full dates, but I need to eliminate the year. Where Sheet2!K are dates and User_Sheet!D1 and E1 are the date range.

I have emboldened the parts of the formula which refer to this to make things easier

=IFERROR(IF($D3<>"",INDEX(Sheet2!$K$3:$K$20000,MATCH(User_Sheet!$B$1,IF((Sheet2!$A$3:$A$20000>User_Sheet!$A3)*[B](Sheet2!$K$3:$K$20000>=User_Sheet!$D$1)*(Sheet2!$K$3:$K$20000< User_Sheet!$E$1)[/B],Sheet2!$E$3:$E$20000),0)),""),"")

 

[GlennUK]

Use Jonmo's formula to generate your flags.

 

[Noz2k]

Try

=AND(TEXT(cell1,"dd/mm")+0>=TEXT(cell2,"dd/mm")+0,TEXT(cell1,"dd/mm")+0<=TEXT(cell3,"dd/mm")+0)

This is similar to what I have used to work out the number of years between Today and the date. However I tried fitting this into my formula, maybe I didn't input it the correct way but it just returned N/A

 

[Jonmo1]

Dependig on your Regional settings, you may need to change the dd/mm to mm/dd