PDA

View Full Version : Date within date range



sindhuja
02-22-2012, 12:33 PM
Hi... can this be done using index function. While comparing the belwo i need to consider column A and E also.

Compare date in column F with the columns B and C and find whether date is with in the range of dates in column B and Column C provided considering the value in column A and column E.

spreadsheet with sample data has been attached for reference.

Kindly assist.:help

-Sindhuja

Bob Phillips
02-22-2012, 05:23 PM
Try this ARRAY formula

=($E2=$A$2:$A$3)*($F2>=$B$2:$B$3)*($F2<=$C$2:$C$3)=1

sindhuja
02-22-2012, 08:08 PM
Thanks Xld..

I tried the array formula but it is not provided the result, if it has more than one same value either in column A or column E.

i have attached the file for reference.

-sindhuja

raji2678
02-22-2012, 09:55 PM
Best way is to use a macro. Loop through all the dates in the source list. If there is even a single one within the range, return true. This will be easy to maintain in the long run as well.

sindhuja
02-22-2012, 11:00 PM
Can anyone please assist with the macro..:help:help

-Sindhuja

raji2678
02-22-2012, 11:19 PM
PFA the solution in the attached sheet

mohanvijay
02-22-2012, 11:27 PM
try this

SUMPRODUCT(--($A$2:$A$5=E2)*($B$2:$B$5<=F2)*($C$2:$C$5>=F2))>0

Bob Phillips
02-23-2012, 02:01 AM
Thanks Xld..

I tried the array formula but it is not provided the result, if it has more than one same value either in column A or column E.

i have attached the file for reference.

-sindhuja

You need to array-enter it, Ctrl-Shift-Enter not just Enter.

I also missed a vital component in my formula, it should be

=SUM(($E2=$A$2:$A$17)*($F2>=$B$2:$B$17)*($F2<=$C$2:$C$17))>0

When you array enter it, you will see it encompassed by { }

sindhuja
02-23-2012, 08:35 PM
Thank you Xld :)

The formula works for me. AS i said earlier i have more number of rows. I copy pasted the formula to around 10000 rows but it took more than 1 hour to calculate the results.

Is there a way to avoid the time delay.

Sindhuja

sindhuja
02-23-2012, 08:37 PM
Thanks for the coding Raji...

I tried coding but the results were not as expected. Am not sure where i went wrong. The result shows only true though it is false. Please assist.

-Sindhuja

raji2678
02-23-2012, 09:11 PM
In the sheet I sent, did the results tally? Beside coding, you will have to do your fair share of debugging as well,in order to find out what went wrong :-)

sindhuja
03-12-2012, 03:14 AM
Hi Xld,

the formula works fine for me.. in addition i need the next column value (ie column D value) instead of true or false.

I tried but i didnt get the results i need.. can you please help me

-sindhuja

Bob Phillips
03-12-2012, 04:03 AM
There is nothing in column D.

sindhuja
03-12-2012, 04:32 AM
Yes in the attached sheet i have not mentioned any value. But the column D is the amount column.

-Sindhuja

Bob Phillips
03-12-2012, 04:40 AM
Maybe this array formula

=IF(($E2=$A$2:$A$3)*($F2>=$B$2:$B$3)*($F2<=$C$2:$C$3)>0,INDEX($D$2:$D$3,MATCH(1,($E2=$A$2:$A$3)*($F2>=$B$2:$B$3)*($F2<=$C$2:$C$3),0)),"")

sindhuja
03-12-2012, 10:23 AM
Please find the attached sheet for your reference. The results are not as per expected.
Kinldy do the needful.

-Sindhuja

Haseeb A
03-12-2012, 01:05 PM
Hello Sindhuja,

Looks like you are on XL2007 or later, If so try SUMIFS.

=SUMIFS(D:D,A:A,F2,B:B,"<="&G2,C:C,">="&G2)
copy down.


Haseeb

sindhuja
03-12-2012, 11:02 PM
Hello Haseeb,


Thanks !! But the results are not as expected.

Please help me .

-sindhuja

Bob Phillips
03-13-2012, 01:15 AM
Is this any better?

=IF(NOT(ISNA(MATCH(1,($F2=$A$2:$A$21)*($G2>=$B$2:$B$21)*($G2<=$C$2:$C$21),0))),
INDEX($D$2:$D$21,MATCH(1,($F2=$A$2:$A$21)*($G2>=$B$2:$B$21)*($G2<=$C$2:$C$21),0)),"")

Haseeb A
03-14-2012, 03:24 PM
...the results are not as expected...

Sindhuja, Can you please attach a sheet with expected results?