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


        • #5
          Originally posted by Mark Wyld View Post
          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...
          Thank Mark Wyld,
          I want to ask if I want to count unique value in specific times. How can I do?


          Comment


          • #6
            Originally posted by Mark Wyld View Post
            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,"&gt;="&amp;INT(MIN(B2:B677))+TIME(10,0,0))-COUNTIF(B2:B677,"&gt;"&amp;INT(MIN(B2:B677))+TIME(17,0,0))
            The "INT(MIN(&lt;range&gt)" giving you the value of the particular day...
            Thank Mark Wyld,
            I want to ask if I want to count unique value in specific times. How can I do?

            Comment


            • #7


              Please start a new thread. Do not post your questions on other people's threads.
              Where there is a will there are many ways. Finding one that works for you is the challenge!

              MS Excel MVP 2010-2016

              Comment

              Working...
              X