If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating the number of entries between 2 times
I am working on a Dispatch records workbook. I am trying to get counts of
entries between 2 time frames. Example, How many entries between 8:00AM and 9:00AM, 9:00AM and 10:00 AM etc. I can get counts using less than, more than, and equal, but not have had much luck with getting counts between 2 values. I would like to also get response times(the time the call is received to when it is dispatched) between 2 times. Any suggestions? |
#2
|
|||
|
|||
Calculating the number of entries between 2 times
Ok, the first thing you have to do is to better define the time intervals.
8:00 AM to 9:00 AM 9:00 AM to 10:00 AM So, if the time is 9:00 AM which interval does that fall in? You might want to use these for the time intervals: 8:00 AM to 8:59 AM 9:00 AM to 9:59 AM 10:00 AM to 10:59 AM Or, you could use these: 8:01 AM to 9:00 AM 9:01 AM to 10:00 AM 10:01 AM to 11:00 AM A1:A100 = times to be counted Use cells to hold the time boundaries for the interval: C1 = lower boundary = 8:00 AM D1 = upper boundary = 8:59 AM =COUNTIF(A1:A100,"="&C1)-COUNTIF(A1:A100,""&D1) -- Biff Microsoft Excel MVP "Bonzai" wrote in message ... I am working on a Dispatch records workbook. I am trying to get counts of entries between 2 time frames. Example, How many entries between 8:00AM and 9:00AM, 9:00AM and 10:00 AM etc. I can get counts using less than, more than, and equal, but not have had much luck with getting counts between 2 values. I would like to also get response times(the time the call is received to when it is dispatched) between 2 times. Any suggestions? |
#3
|
|||
|
|||
Calculating the number of entries between 2 times
P.S.
Excel, trying to be "helpful", may show the result of the formula as a date. Just change the format to either General or Number. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, the first thing you have to do is to better define the time intervals. 8:00 AM to 9:00 AM 9:00 AM to 10:00 AM So, if the time is 9:00 AM which interval does that fall in? You might want to use these for the time intervals: 8:00 AM to 8:59 AM 9:00 AM to 9:59 AM 10:00 AM to 10:59 AM Or, you could use these: 8:01 AM to 9:00 AM 9:01 AM to 10:00 AM 10:01 AM to 11:00 AM A1:A100 = times to be counted Use cells to hold the time boundaries for the interval: C1 = lower boundary = 8:00 AM D1 = upper boundary = 8:59 AM =COUNTIF(A1:A100,"="&C1)-COUNTIF(A1:A100,""&D1) -- Biff Microsoft Excel MVP "Bonzai" wrote in message ... I am working on a Dispatch records workbook. I am trying to get counts of entries between 2 time frames. Example, How many entries between 8:00AM and 9:00AM, 9:00AM and 10:00 AM etc. I can get counts using less than, more than, and equal, but not have had much luck with getting counts between 2 values. I would like to also get response times(the time the call is received to when it is dispatched) between 2 times. Any suggestions? |
Thread Tools | |
Display Modes | |
|
|