Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Count Times Between 2 Specified Times

  1. #1
    Join Date
    15th September 2006
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    11th July 2006
    Posts
    36

    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...

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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)))

  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Count Of Times In Column
    By pytelium in forum Excel General
    Replies: 4
    Last Post: April 26th, 2009, 06:22
  2. Count Of Date & Times By Specific Date Ignoring Times
    By Downloaders in forum Excel General
    Replies: 3
    Last Post: March 6th, 2008, 10:12
  3. Convert Real Times to Decimal Times
    By Donnah in forum Excel General
    Replies: 2
    Last Post: September 6th, 2006, 16:50
  4. count the times that the three conditions are positive
    By Chipcom in forum Excel General
    Replies: 5
    Last Post: June 27th, 2005, 12:42

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
  •  
porno