# Range by Date (Not Including Year)

#### Noz2k

##### Well-known Member
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.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Call back by using what Excel command? is it by using Data/Filter/Advanced Filter?

No, it's inside an Index Match function

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?

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?

Try

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

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

Code:
``=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)),""),"")``

Use Jonmo's formula to generate your flags.

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

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

Replies
1
Views
143
Replies
3
Views
220
Replies
8
Views
187
Replies
6
Views
263
Replies
7
Views
303

1,221,507
Messages
6,160,216
Members
451,630
Latest member
zxhathust

### 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