Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Count Times Between 2 Specified Times

1. I agreed to these rules
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. Member
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. ## 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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

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

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

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