How to count times between 2 specified times
Requirement:
Each month the user has 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 the user has tried for 10 am to 5 pm is:
=COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))
It gives a result of 676, and the user knows from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.
Solution:
The times are actually dates and times, so the values aren't 1:21:19 AM but 9/14/2006 1:21:19 AM.
Modifying the 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())" giving you the value of the particular day...
Obtained from the OzGrid Help Forum.
Solution provided by Mark Wyde.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA script to count files/subfolders using a range from WB for the root folder |
| How to VBA code to count duplicates FAST |
| How to set a countdown timer to beep under 10 seconds |
| How to count the number of cells containing a comment that contains the word LATE |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
