Collapsing Dates into Categories

[Craig]

In Column F, I have a Date-formatted column. In Column
J, I have "Beginning Date" and Column K "Ending Date.
Column I is a "Case No" variable from 1-10 (for 10
different possibilities). Each combination of start/end
dates is unique.
I would like to format Column G so that if Column F has
falls within the range of Column J (beginning date) and
Column K (ending date), that the "Case No." variable in
Column I is produced...

thanks for your help.

Craig

 

[Frank Kabel]

Hi
try
=IF(AND(F1>=J1,F1<=K1),I1,"")

 

[craigc]

Thank you! How do I extend the formula to address
beginning dates I2-end date J2 to be assigned
to "group2", I3-J3 to "group 3", etc. within the same
cell formula?

thanks,
Craig

 

[Frank Kabel]

Hi
not really sure what you mean with this. Could you post some example
data (plain text - no attachment please)

 

[Guest]

Basically, if the date in Column A below falls within the range of Column D to E, then I would like Column B to match the appropriate group (Column C) for the range in D to E where A matches.....
(see data sample below)....thanks!


A B C D E
Date Match Group begin end
10/16/2001 1 1/10/01 1/17/01
11/19/2001 2 2/3/01 2/12/01
11/19/2001 3 3/1/01 3/12/01
12/3/2001 4 3/28/01 4/5/01
1/31/2002 5 4/17/01 4/25/01
2/28/2002 6 6/7/01 6/18/01
5/3/2002 7 8/21/01 8/31/01
6/24/2002 8 7/6/02 7/16/02
7/11/2002 9 11/13/02 11/24/02
7/31/2002 10 7/6/03 7/27/03
9/3/2002 11 8/19/03 8/28/03
9/13/2002 12 9/21/03 10/1/03
9/20/2002 13 12/3/03 12/13/03
9/23/2002 14 12/7/03 12/15/03
9/24/2002 15 1/5/04 1/15/04
16 3/3/04 3/14/04
10/2/2002 17 3/3/04 3/14/04
10/3/2002 18 3/23/04 4/1/04
10/8/2002 19 4/16/04 4/27/04
10/8/2002 20 5/13/04 5/23/04
10/8/2002 21 6/16/04 9/18/01
10/11/2002 22 7/14/04 7/15/04
10/11/2002
11/8/2002
11/14/2002
11/15/2002
12/3/2002
12/6/2002

1/20/2003
1/22/2003
1/27/2003
1/29/2003
1/29/2003
1/29/2003
1/29/2003
1/29/2003
1/29/2003
1/30/2003
2/7/2003
2/7/2003
2/13/2003
2/13/2003
2/17/2003
2/17/2003
2/25/2003
2/25/2003
2/27/2003
2/27/2003
3/7/2003
3/7/2003
3/7/2003
3/10/2003
3/12/2003
3/13/2003
3/13/2003
3/14/2003
3/14/2003
3/17/2003
3/17/2003
3/17/2003
3/18/2003
3/18/2003
3/19/2003
3/20/2003
3/20/2003
3/20/2003
3/20/2003
3/20/2003
3/20/2003
3/20/2003
3/21/2003
3/21/2003
3/24/2003
3/24/2003
3/24/2003
3/24/2003
3/24/2003
3/24/2003
3/27/2003
3/27/2003
4/2/2003
4/4/2003
4/9/2003
4/9/2003
4/11/2003
4/11/2003
4/14/2003
4/15/2003
4/15/2003
5/1/2003
5/2/2003
5/2/2003
5/2/2003
5/7/2003
5/7/2003
5/16/2003
6/12/2003
6/13/2003
7/14/2003
7/14/2003
7/17/2003
7/17/2003
7/17/2003
7/22/2003
7/23/2003
7/23/2003
7/23/2003
7/23/2003
8/1/2003
9/4/2003
9/8/2003
9/8/2003
9/8/2003
9/18/2003
9/22/2003
9/25/2003
9/30/2003
10/3/2003
10/6/2003
10/6/2003
10/29/2003
10/29/2003
10/29/2003
11/10/2003
11/14/2003
11/14/2003
12/11/2003
12/15/2003
12/16/2003
12/17/2003
1/12/2004
1/22/2004
1/29/2004
2/4/2004
3/18/2004
4/6/2004
4/9/2004
4/14/2004
4/14/2004
4/14/2004
4/20/2004
4/29/2004
5/5/2004
5/7/2004
5/10/2004
5/11/2004
5/11/2004
5/25/2004
5/28/2004
5/28/2004
6/2/2004
6/2/2004
6/9/2004
6/10/2004
6/11/2004
6/14/2004
6/16/2004
6/16/2004
6/22/2004
6/25/2004
6/29/2004
7/6/2004

 

[Myrna Larson]

Don't know what others have proposed, but I see that your 22 rows of date
ranges have gaps. i.e. group 1 is 1/10/01-1/17/01, and group 2 starts at
2/3/01, and the first 9 dates in column A DON'T fall into an existing group.

Given that (assuming what you posted is typical of the data), you can't use
INDEX and MATCH, but an array formula (entered with CTRL+SHIFT+ENTER) may work

=SUM((A2>=$D$2:$D$23)*(A2<=$E$2:$E$23)*($C$2:$C$23))

That will return a 0 with an "illegal" date. Copy down as far as needed.

I used 2 as the top row of the data (headers in row 1?) and 23 as the bottom.
Change these to fit your layout.

Basically, if the date in Column A below falls within the range of Column D

to E, then I would like Column B to match the appropriate group (Column C) for
the range in D to E where A matches.....