Running count of occurences within a time frame

M

MartyMoats

New Member
Joined
May 25, 2018
Messages
5
I have a large spreadsheet with events that have happened, noting (across the row) the time and a lot of information about that event. I wish to add a column that counts how many times an event has happened in the last given amount of time. So, for example, in the following spreadsheet where time is the first column followed by a lot of columns, I want to add a column that states how many times an event has happened in the last 6 hours. How do I do that?

1/1 1pm event details....
1/1 3pm event details....
1/1 10pm event details....
1/3 4am event details....
1/3 9am event details....
and so on.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
[TABLE="width: 885"]
<colgroup><col><col span="3"><col><col span="7"></colgroup><tbody>[TR]
[TD]date_time[/TD]
[TD]event[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2018 09:35[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2018 10:47[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2018 09:35[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]how many times did E4 occur 4/5/2018 to 9/5/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2018 10:47[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/05/2018 09:35[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/05/2018 10:47[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/05/2018 09:35[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/05/2018 00:00:01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/05/2018 10:47[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/05/2018 23:59:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2018 09:35[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2018 10:47[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/05/2018 09:35[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/05/2018 10:47[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/05/2018 09:35[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/05/2018 10:47[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD]formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/05/2018 09:35[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=SUMPRODUCT(($A$2:$A$31>=$E$8)*($A$2:$A$31<=$E$9)*($B$2:$B$31=$E$7))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/05/2018 10:47[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018 09:35[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018 10:47[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2018 09:35[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]easy to specify time periods not just whole days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2018 10:47[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/05/2018 09:35[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/05/2018 10:47[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/05/2018 09:35[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/05/2018 10:47[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/05/2018 09:35[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/05/2018 10:47[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/05/2018 09:35[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/05/2018 10:47[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2018 09:35[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2018 10:47[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sorry, I wasn't clear with my original thread. The second column, labeled event, isn't needed because every row is an event. The spreadsheet is an event log. When I wrote "event details" in my original post, I was providing more color to what my event log looks like, but for the purpose of my request, only the first column, the date/time, is needed or relevant. I want to add a column to the log so that, for each row, it tells me how many times an event (i.e. a row entry) has happened in the last given time increment (6 hours, for example - in practice, I will probably try different time periods).
 
Upvote 0
Or, rather than adding a column, having a different tab or pivot table showing for every 6 hours how many events there were would be good. Actually, I can do this with a pivot table except that it would only show nonzero numbers. Time increments with no events would be omitted, but I need the zero's in there.
 
Upvote 0
Sorry, scratch my last post, saying the different tab or pivot table would be ok. I need it in the form described in the post before. First day back after long weekend, brain not in gear yet.
 
Upvote 0
Welcome to the MrExcel board!

See if this does what you want. When my sheet was calculated my local date/time was 29 May 2018 10:32 PM
Note that my dates are in d/m/y format and you can change cell D1 to check for a different time period (in hours)

Excel Workbook
ABCD
1Date/TimePrevious number of hours:6
227/05/2018 07:54 PMCount:4
327/05/2018 10:28 PM
428/05/2018 04:05 AM
528/05/2018 10:19 AM
628/05/2018 02:13 PM
728/05/2018 03:31 PM
828/05/2018 05:05 PM
928/05/2018 11:43 PM
1029/05/2018 02:05 AM
1129/05/2018 07:43 AM
1229/05/2018 08:08 AM
1329/05/2018 10:12 AM
1429/05/2018 10:21 AM
1529/05/2018 11:39 AM
1629/05/2018 11:48 AM
1729/05/2018 01:32 PM
1829/05/2018 01:40 PM
1929/05/2018 01:52 PM
2029/05/2018 02:31 PM
2129/05/2018 04:29 PM
2229/05/2018 06:45 PM
2329/05/2018 08:57 PM
2429/05/2018 09:07 PM
2529/05/2018 10:18 PM
26
Event log



If you are only entering "past" date/time values then you would only need

=COUNTIF(A2:A25,">"&NOW()-D1/24)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
406
Fluff
Fluff
ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
275
Alex Blakenburg
A
N
  • Question Question
Auto Sort for Leaderboard
Replies
0
Views
564
NTLKnight
N
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
647
felixstraube
F
J
  • Locked
  • Question Question
Still need help on a daily summary sheet
Replies
1
Views
594
Fluff
Fluff

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top