+ Reply to Thread
Results 1 to 3 of 3

Count times between 2 times and 2 dates

  1. #1
    Jeremy Ellison
    Guest

    Count times between 2 times and 2 dates

    This is difficult and I'm lost! Here is the data (omitting not needed)

    A D E
    Control # Date Time



    2 questions:

    1. What formula would I use if I wanted to find out the number of #'s
    ocurring on Monday between 1/1/06 and 3/31/06 (1st quarter of the year).
    1.a IS there a way not to count diplicates of the control #
    only... it would
    be ok to count duplicate dates, just not duplicate of
    control #'s -
    which could occur unfortunately becuase of the way the
    data is used.

    2. What formula would I use if I wanted to find out the number of #'s
    occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06?
    2.a SAME as 1.a


    I tried using the sumproduct formula and am completely screwing that up...
    please help!

  2. #2
    Max
    Guest

    Re: Count times between 2 times and 2 dates

    Assume data in the table (cols A, D, E) is from row2 to row100,
    col D contains real dates,
    col E contains time expressed as text numbers, eg: "0000", "0100", etc

    Use 2 empty cols to the right, say, cols G & H
    Put in G2: =A2&"_"&D2
    Put in H2: =IF(COUNTIF($G$2:G2,G2)>1,"","x")
    Select G2:H2, copy down to H100
    (Col H will flag unique "Control # - Date" with an "x")

    Then, to extract the the number of Control #'s
    ocurring on Monday between 1/1/06 and 3/31/06
    (counting only unique "Control # - Date")

    we could put in say, I2:

    =SUMPRODUCT((H2:H100="x")*(WEEKDAY(D2:D100)=1)*(D2:D100>= --"1-Jan-2006")*(D
    2:D100<= --"31-Mar-2006"))

    And to retrieve the number of Control #'s occurring
    between 0000 hours and 0400 hours
    between 1/1/06 and 3/31/06
    (again, counting only unique "Control # - Date")

    we could put in say, J2:

    =SUMPRODUCT((H2:H100="x")*(E2:E100>= "0000")*(E2:E100<=
    "0400")*(D2:D100>= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006"))

    Adapt the ranges to suit
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "Jeremy Ellison" <[email protected]> wrote in message
    news:[email protected]...
    > This is difficult and I'm lost! Here is the data (omitting not needed)
    >
    > A D E
    > Control # Date Time
    >
    >
    >
    > 2 questions:
    >
    > 1. What formula would I use if I wanted to find out the number of #'s
    > ocurring on Monday between 1/1/06 and 3/31/06 (1st quarter of the year).
    > 1.a IS there a way not to count diplicates of the control #
    > only... it would
    > be ok to count duplicate dates, just not duplicate of
    > control #'s -
    > which could occur unfortunately becuase of the way the
    > data is used.
    >
    > 2. What formula would I use if I wanted to find out the number of #'s
    > occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06?
    > 2.a SAME as 1.a
    >
    >
    > I tried using the sumproduct formula and am completely screwing that up...
    > please help!




  3. #3
    Max
    Guest

    Re: Count times between 2 times and 2 dates

    And if we don't want to count only unique "Control # - Date"'s
    just remove this condition from the formula: .. (H2:H100="x")* ..
    viz., use:

    In I2:
    =SUMPRODUCT((WEEKDAY(D2:D100)=1)*
    (D2:D100>= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006"))

    In J2:
    =SUMPRODUCT((E2:E100>= "0000")*(E2:E100<="0400")*
    (D2:D100>= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006"))

    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1