Announcement

Collapse
No announcement yet.

Count Times Between 2 Specified Times

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Count Times Between 2 Specified Times

    I thought I found a formula that would work, but it's not working.

    Each month I have to count the number of service tickets that have arrived between certain time ranges. They want to gauge during what times we seem to get the biggest batch of service requests.

    6 am to 10 am
    10 am to 5 pm
    5 pm to 6 pm
    6 pm to 6 am

    The format of the cells are:

    1:21:19 AM
    1:28:08 AM
    1:35:48 AM
    1:49:19 AM
    2:17:02 AM
    7:14:38 AM
    7:29:12 AM
    8:08:28 AM
    8:51:48 AM
    8:54:19 AM

    The formula I tried for 10 am to 5 pm:

    =COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))

    It gives a result of 676, and I know from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.

    Any ideas how to get it to work?

    Diona

  • #2
    Re: Time Range Countif Formula

    My guess is that the times are actually dates and times, so the values aren't 1:21:19 AM but 9/14/2006 1:21:19 AM. Try modifying your formula to

    =COUNTIF(B2:B677,">="&INT(MIN(B2:B677))+TIME(10,0,0))-COUNTIF(B2:B677,">"&INT(MIN(B2:B677))+TIME(17,0,0))

    The "INT(MIN(<range>))" giving you the value of the particular day...

    Comment


    • #3
      Re: Time Range Countif Formula

      Personally I would use a Pivot Tables and Group by date or DCOUNT

      My 3rd choice would be SUMPRODUCT, that is
      =SUMPRODUCT(($A$1:$A$10>=TIME(10,0,0))*($A$1:$A$10<=TIME(17,0,0)))

      Comment


      • #4
        Re: Time Range Countif Formula

        6 am to 10 am

        =SUM(COUNTIF($B$2:$B$667,{">=0.25",">0.416666666666667"})*{1,-1})

        10 am to 5 pm

        =SUM(COUNTIF($B$2:$B$667,{">=0.416666666666667",">0.708333333333333"})*{1,-1})

        5 pm to 6 pm

        =SUM(COUNTIF($B$2:$B$667,{">=0.708333333333333",">0.75"})*{1,-1})

        6 pm to 6 am

        =SUM(COUNTIF($B$2:$B$667,{">=0.75",">1",">=0",">0.25"})*{1,-1,1,-1})

        HTH
        Kris

        ExcelFox

        Comment

        Working...
        X