Excell Time Occurence

[TurtleontheRun]

Hello all,

I've been trying to figure out a way to tally how many times an occurrence happens between a time frame. It's a little hard to explain, so I've provided an example below.

The columns for time run from J4 to J83. I need a formula that would be calculate that a time appeared say between 1600-1659, 4 times.

I apologise for the confusing explanation. I'll be happy to answer any questions. I have tried numerous countif/countifs functions after a strenuous search and haven't been able to adjust it to either function properly or to count correctly. Any suggestions would be greatly appreciated.



[TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl71, width: 154, bgcolor: transparent, colspan: 2"]Reported
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Time
[/TD]
[TD="class: xl70, bgcolor: transparent"]Date
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]8:05
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]9:48
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]11:24
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:19
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:35
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:48
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]16:42
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]16:42
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/01/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]16:05
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/02/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:17
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/05/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]15:06
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/05/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:11
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/06/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]14:33
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/06/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:37
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/06/2017
[/TD]
[/TR]
[TR]
[TD="class: xl68"]16:20
[/TD]
[TD="class: xl69, bgcolor: transparent"]09/06/2017
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl69, width: 64, bgcolor: transparent"]Time
[/TD]
[TD="class: xl71, width: 64, bgcolor: transparent"]# of reports
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]00:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]01:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]02:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]03:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]04:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]05:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]06:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]07:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]09:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]10:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]11:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]12:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]6
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]13:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]14:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]15:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]16:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]17:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]18:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]19:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]20:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]21:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]22:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]23:00
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Suppose your second table is in A2:A25 (00:00 through 23:00). I suggest entering 24:00 (actually 1) into A26, which you can hide. Format A2:A26 as Custom [hh]:mm if you want to display A26.

Then enter the following into B2 and copy down through B25:

=COUNTIFS($J$4:$J$83, ">=" & A2, $J$4:$J$83, "<" & A3)
or
=COUNTIF($J$4:$J$83, "<" & A3) - COUNTIF($J$4:$J$83, "<" & A2)

Format column B as General, if necessary. Use the first formula if the Excel file is saved as "xlsx" or "xlsm". Use the second formula if the Excel file is saved as "xls".

-----

It would be more efficient to use the FREQUENCY function. For example, enter the following into C2 and copy down through C25, which you can hide (and A26 is not needed):

=--TEXT(A2+TIME(0,59,59),"[h]:m:ss")

That presumes that the times in column J are entered as constants, not calculated, and they are accurate to the 1 minute (or 1 second). The double negate ("--") converts text to numeric Excel time. The TEXT function rounds to the internal binary representation of the hour plus 59 min 59 sec. Rounding is important because FREQUENCY compares the binary representation, not the displayed appearance.

Then select B2:B25 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=FREQUENCY(J4:J83, C2:C25)

 

[wideboydixon]

No helper columns or rows necessary:

Book1
	A	B
3	Time	# of reports
4	00:00	0
5	01:00	0
6	02:00	0
7	03:00	0
8	04:00	0
9	05:00	0
10	06:00	0
11	07:00	0
12	08:00	1
13	09:00	1
14	10:00	0
15	11:00	1
16	12:00	6
17	13:00	0
18	14:00	1
19	15:00	1
20	16:00	4
21	17:00	0
22	18:00	0
23	19:00	0
24	20:00	0
25	21:00	0
26	22:00	0
27	23:00	0
Sheet1

Cell Formulas
Range	Formula
B4	=COUNTIFS($J$4:$J$83,">="&$A4,$J$4:$J$83,"<"&$A4+TIME(1,0,0))

WBD

 

[joeu2004]

No helper columns or rows necessary:
[....]
=COUNTIFS($J$4:$J$83,">="&$A4,$J$4:$J$83,"<"&$A4+TIME(1,0,0))

Right. Klunk! And for completeness, my other suggestions can be written as:

=COUNTIF($J$4:$J$83, "<" & A2 + TIME(1,0,0)) - COUNTIF($J$4:$J$83, "<" & A2)

=FREQUENCY(J4:J83, --TEXT(A2:A25 + TIME(0,59,59), "[h]:m:ss"))

Again, the FREQUENCY formula is array-entered: select B2:B25 and press ctrl+shift+Enter instead of just Enter.

 

[TurtleontheRun]

Thank you both! This has made my monthly audits fly!