Range by Date (Not Including Year)

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Call back by using what Excel command? is it by using Data/Filter/Advanced Filter?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Well-known Member
Joined
Mar 15, 2011
Messages
688

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

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

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688

ADVERTISEMENT

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)),""),"")
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
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:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Dependig on your Regional settings, you may need to change the dd/mm to mm/dd
 

Forum statistics

Threads
1,141,224
Messages
5,705,116
Members
421,378
Latest member
CarlosDuran

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top