Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Frisco, Texas, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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)

    If I haven't answered your question, repost with more details.
    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Frisco, Texas, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •