Between Dates issue

[Noz2k]

So I have a user sheet which looks up some values from sheet2 where the dates fall between a certain range. Currently this works independant of the year.

The code is as follows


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

<TEXT(USER_SHEET!$E$1,"DD p code]< mm?)+0),Sheet2!$E$3:$E$20000),0)),??),??)[ code]<TEXT(USER_SHEET!$E$1,?DD mm?)+0),Sheet2!$E$3:$E$20000),0)),??),??)<TEXT(USER_SHEET!$E$1,?DD>

This is fine if I'm looking at dates between for example 01/05/2011 and 10/05/2011. It returns all the relevant data for all years.

However a problem occurs when the date range crosses a year, for example if I wanted to return all values between 25/12/2010 and 05/01/2011.

Any idea how to solve this issue?

 

[Noz2k]

Is it possible to use some sort of between function in excel as opposed to > and less than?<?

Otherwise I'm thinking I could possibly put an if clause in there which says that if the years of User_Sheet D1 & E1 are the same, then show dates from D1 up to and including 31/12 and show dates from 01/01 up to E1.

But struggling to get my head around that

 

[Andrew Poulsom]

Is it?

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

 

[Noz2k]

Thanks Andrew, it may well be but unfortunately not all the code you posted has shown up, think that's to do with the "<" symbol

 

[Andrew Poulsom]

Sorry:

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

 

[Noz2k]

That's alright, I did the same when I originally posted up my code.

That's not it unfortunately. That works if all I wanted to return were the dates within that period. But need to return all dates regardless of year that fall in that period.

For instance

name1 30/12/2001
name2 31/12/2010
name3 01/03/2006
name4 02/01/2003

Where Start Date (D1) = 25/12/2010
And End Date (E1) = 05/01/2011

I would want that to return names 1,2 and 3

 

[Andrew Poulsom]

To sum the dates for your sample data:

=SUM((DATE(YEAR($D$1)+(TEXT(B1:B4,"dd/mm")<TEXT($D$1,"dd/mm")),MONTH(B1:B4),DAY(B1:B4))>=$D$1)*(DATE(YEAR($D$1)+(TEXT(B1:B4,"dd/mm")<TEXT($D$1,"dd/mm")),MONTH(B1:B4),DAY(B1:B4)) < $E$1))

confirmed with Ctrl+Shift+Enter. So I think your formula becomes:

=IFERROR(IF($A3<>"",INDEX(Sheet2!$A$3:$A$20000,MATCH(User_Sheet!$B$1,IF((Sheet2!$A$3:$A$20000>User_Sheet!$A3)*(DATE(YEAR(User_Sheet!$D$1)+(TEXT(Sheet2!$K$3:$K$20000,"dd/mm")<TEXT(User_Sheet!$D$1,"dd/mm")),MONTH(Sheet2!$K$3:$K$20000),DAY(Sheet2!$K$3:$K$20000))>=User_Sheet!$D$1)*(DATE(YEAR(User_Sheet!$D$1)+(TEXT(Sheet2!$K$3:$K$20000,"dd/mm")<TEXT(User_Sheet!$D$1,"dd/mm")),MONTH(Sheet2!$K$3:$K$20000),DAY(Sheet2!$K$3:$K$20000)) < User_Sheet!$E$1),Sheet2!$E$3:$E$20000),0)),""),"")

although I can't test it as I don't have Excel 2007 where I am at the moment.