Date Range Again

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Thanks for the input dave.

I am trying to figure out how to take a column of dates
and extract a specific range of dates out (ultimatly
counting the extracted dates to come up with how many
people are being hired in a given time)

I tried the advanced filter. however I need a formula
that can allow someone to add dates and the program be
able to update from the additional data.
 
thanks again
-----Original Message-----
I'll show how to handle this for both the case where you might have
duplicates and where you won't (or don't care about duplicates).

This shows what the sheet will look like:



A B C D E F G H I
1 Date Start Date End Date
2 9/1/03 9/3/03 9/7/03 9/3/03 9/3/03
3 9/2/03 9/4/03 9/4/03
4 9/3/03 4 9/4/03 4 9/6/03
5 9/4/03 5 9/6/03 5 9/7/03
6 9/4/03 6 9/7/03
7 9/6/03 7 7
8 9/7/03 8 8
9 9/8/03



in E2 and copied down:

=IF(AND(A2>=$C$2,A2<=$D$2),ROW(),"")

in F2 and copied down:

=IF(ISNUMBER(SMALL(E:E,ROW(1:1))),INDIRECT("A" &
SMALL(E:E,ROW(1:1)),TRUE),"")

This handles the case where the origina list doesn't have duplicates (or
where we don't care if they get into the second list)

For the case where duplicates need to be filtered out:

in H2 and copied down:

=IF(AND(A2>=$C$2,A2<=$D$2, COUNTIF($A$2:A2,A2)=1),ROW (),"")

in I2 and copied down:

=IF(ISNUMBER(SMALL(H:H,ROW(1:1))),INDIRECT("A" &
SMALL(H:H,ROW(1:1)),TRUE),"")

HTH

-Dave




.
Click to expand...
 
Back
Top