# Thread: Searching through dates (2000)

1. ## Searching through dates (2000)

I'm hoping someone can help me find a solution to this problem, I have 5 groups of dates as follows:
Cell G value
E13 1 10/20/02
E14 2 09/20/02
E15 3 11/02/02
E16 1 09/20/02
E17 2 10/12/02
E18 3 10/20/02
E19 1 09/14/02
E20 2 11/20/02
E21 3 09/02/02

I have set up 2cells with date ranges such as:
October November
A1=09/15/02 B1=10/16/02
A2=10/15/02 B2=11/15/02

I would like to get a count of how many dates from the group fall within the date range, for instance, how many dates fall within the October date range from the E13,E16,E19 group. How many fall within the October range from E14,E17,E20 group; How many fall within the October range from the E15,E18,E21 group. Then the same thing for November.

Thanks, any help would be appreciated.
NSB001

2. ## Re: Searching through dates (2000)

Not sure exactly what you are looking for. If I understand correctly: (F has values 1,2,3,etc and G has dates)

All of these are ARRAY formulas confirm with ctrl-shift-enter not enter:

This will give you where the Fcol has a 1 and it is between A1 and A2(october)
=COUNT(IF((\$F\$13:\$F\$21=1)*(\$G\$13:\$G\$21>=\$A\$1)*(\$G\$ 13:\$G\$21<=\$A\$2),\$G\$13:\$G\$21))

This will give you where the Fcol has a 2 and it is between A1 and A2 (october)
=COUNT(IF((\$F\$13:\$F\$21=2)*(\$G\$13:\$G\$21>=\$A\$1)*(\$G\$ 13:\$G\$21<=\$A\$2),\$G\$13:\$G\$21))

This will give you where the Fcol has a 3 and it is between A1 and A2(october)
=COUNT(IF((\$F\$13:\$F\$21=3)*(\$G\$13:\$G\$21>=\$A\$1)*(\$G\$ 13:\$G\$21<=\$A\$2),\$G\$13:\$G\$21))

This will give you where the Fcol has a 1 and it is between B1 and B2 (November)
=COUNT(IF((\$F\$13:\$F\$21=1)*(\$G\$13:\$G\$21>=\$B\$1)*(\$G\$ 13:\$G\$21<=\$B\$2),\$G\$13:\$G\$21))

This will give you where the Fcol has a 2 and it is between B1 and B2 (November)
=COUNT(IF((\$F\$13:\$F\$21=2)*(\$G\$13:\$G\$21>=\$B\$1)*(\$G\$ 13:\$G\$21<=\$B\$2),\$G\$13:\$G\$21))

This will give you where the Fcol has a 3 and it is between B1 and B2 (November)
=COUNT(IF((\$F\$13:\$F\$21=3)*(\$G\$13:\$G\$21>=\$B\$1)*(\$G\$ 13:\$G\$21<=\$B\$2),\$G\$13:\$G\$21))

This will give you the number of days in the range between A1 and A2 (this is NOT an ARRAY)
=COUNTIF(\$G\$13:\$G\$21,">="&A1)-COUNTIF(\$G\$13:\$G\$21,">"&A2)

This will give you the number of days in the range between B1 and B2 (this is NOT an ARRAY)
=COUNTIF(\$G\$13:\$G\$21,">="&B1)-COUNTIF(\$G\$13:\$G\$21,">"&B2)

Steve

3. ## Re: Searching through dates (2000)

I will give it a try.

Thanks for the help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•