Return a value if a date lies between two dates

[Dave D]

Hi, I'm trying to return the period from the table below,
based on any particular date. e.g. Cell A1 in Sheet1
might contain 26/05/04. I want to check the table in
Sheet2, cells F2:H7 (or F3:H7 without the titles!) and
return the answer 3 into cell A2 in Sheet1.

I'm sure it's probably an array function but haven't got
the hang of them yet!


Period Start date End date
1 26/03/2004 23/04/2004
2 24/04/2004 22/05/2004
3 23/05/2004 20/06/2004
4 21/06/2004 19/07/2004
5 20/07/2004 17/08/2004

 

[Domenic]

Hi Dave,

Try the following...

=SUMPRODUCT((Sheet1!A1>=Sheet2!$G$3:$G$7)*(Sheet1!A1<=Sheet2!$H$3:$H$7),S
heet2!$F$3:$F$7)

Hope this helps!

 

[Dave D]

Excellent - thanks to both!

 

[hgrove]

Harry Bo wrote...
...

You only need to have the end date for a period:
Period End Date
0 26/03/04
1 23/04/04
2 22/05/04
3 20/06/04
4 19/07/04
5 17/08/04

If your Period numbers are in cells A20: A25 and your dates in
B20:B25 and your lookup date is in cell A18

use: =OFFSET(B20,MATCH(A18,$B$20:$B$25,1)-1,-1,1,1)

Note, any date prior to 26/3/04 or your 1st date will return N/A
and any date after your last date will return 5

...

Actually the date 17/08/04 would also return 5. Following the logic o
the OP's table, your last date value should be 18/08/04, and th
corresponding period entry could be #N/A so that date entries befor
the earliest and on or after the last (which would now be the day afte
the last day of the last period) would both lead your formula to retur
#N/A.

Also, in all cases your formula returns one less than what the OP ha
requested. Given your table above, enter the OP's sample date
26-May-2004 in cell A18, and your formula returns 2 rather than 3. Tha
could be fixed by changing your periods to 1 through 5 (with #N/A fo
the last entry). But if the OP's periods began with 1, it'd be easie
still just to use

=MATCH(A18,B20:B25)