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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

GlennUK

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

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top