counting entries per hour

Hi

Perhaps try a Pivot Table... By dragging date & time to column you can group these to display balances by hour.

Feel free to post back if unsure how to build pivot and I (or someone else) can give step by step guidance. But if that's the case please give us a better idea about what data is in the table.

Regards
Jon

Jon,

Thanks for the quick reply! I am not familiar with the pivot table.

Regarding the worksheet, here's the first 5 rows of data. You will see columns 4 and 5 are the date and time stamps. There are a total of 15 columns (A through O). This particular log has 600 rows of data in a 30-hour time period.

What I want to calculate is how many entries (rows) are within 2100<>2200, 2200<>2300, 2300<>0000, 0000<>0100, etc. The time is in 24-hour clock format.

QSO: 14041 CW 11/3/2007 2106 K0RC 1 U 62 ND N6TV 11 B 72 SCV
QSO: 14042 CW 11/3/2007 2107 K0RC 2 U 62 ND N1LN 9 U 64 NC
QSO: 14044 CW 11/3/2007 2109 K0RC 3 U 62 ND K5YA 16 U 55 STX
QSO: 14045 CW 11/3/2007 2110 K0RC 4 U 62 ND N4EEB 13 U 78 NFL
QSO: 14046 CW 11/3/2007 2111 K0RC 5 U 62 ND N3KS 13 U 75 MDC

I was trying to build a nested =countif formula that compare the first two digits of the time in the previous row and further check if the date had changed.

My immediate goal is to get the rates calculated and stored in 48 cells. I'll eventually create a chart using this data.

Thanks for any guidance with this!

Robert Chudek

Hi Rob

I'd start off by ading a new column (new time). I've noted that the time is not entered as a time format so this could cause problems in the pivot. The new column should create a new time output and the formula should read:

=SUBSTITUTE(TEXT(E2,"00-00"),"-",":")+0

(Copy this this end of range and format [Format->Cells->Number] as hh:mm)

Now you can pivot the range:

Select the entire data table -> Data -> Pivot Table
Choose MS Office Excel List or Database -> Next
Make sure the correct selection is chosen -> Next
Click Layout

Drag "New Time" to the Column segment
Drag Date to the Row segment
Drag Old Time to the Data segment -> double click the field and choose "Count"
Click Ok -> Ok -> New Worksheet -> Finish

Now, on the pivot table right-click the New Time field (it will probably apear in grey) -> Group & Show Detail -> Group -> Hours (make sure only Hours are chosen).

Hopeully this will give you desired result. You can see balances per hour per date.

See more on pivot tables and grouping here.

For more basics on ivot tables see here.

Regards
Jon

Thank you Jon,

A very nice, detailed write up. I'll give this a try tomorrow as I'm running out of steam here tonight (3:30 AM local!).

Best regards,

Robert Chudek

Okay, I have experimented with Pivot Tables. They work okay but it is not the output format I'm looking for.

Using the layout I posted in my second message, I have converted the 4th and 5th columns into the proper date and time formats Excel recognizes. So I am all set with that portion of the calcuations.

Now I need a formula that will count the number of events in each 1-hour period and give me the number of occurances. There may be 2,3 or 4 days of accumulated date & time stamps between the first and the last recorded event. The rate per hour may vary from 0 events to 300 events.

This problem can be viewed as a simple (?) traffic counter that counts the number of cars driving by during a couple of days or weeks. Each car triggers a date / time row in the data file.

TIA for any new insight!

Try creating a column containing the start time/date of each interval, e.g. in Q2:Q10

1-Nov-2007 00:00
1-Nov-2007 01:00
1-Nov-2007 02:00
1-Nov-2007 03:00
1-Nov-2007 04:00
1-Nov-2007 05:00
1-Nov-2007 06:00
1-Nov-2007 07:00
1-Nov-2007 08:00

and continue on down for all the intervals you need

Now in R2 use this formula copied down

=SUMPRODUCT((D$1:D$600+E$1:E$600>=Q2)*(D$1:D$600+E$1:E$600< Q2+"1:00"))

Barry,

I think your formula is on the right track, but I'm getting a #VALUE error. Here's a snippet of the real data and your original formula rewritten for these columns:

H2 =SUMPRODUCT((D$2:D$600+E$2:E$600>=G2) *
(D$2:D$600+E$2:E$600< G2+"1:00"))


- D E F G H
1 Date Time Period Count
2 30-Nov-07 23:43 21:00 #VALUE!
3 30-Nov-07 23:44 22:00 #VALUE!
4 30-Nov-07 23:44 23:00 #VALUE!
5 30-Nov-07 23:45 00:00 #VALUE!
6 30-Nov-07 23:47 01:00 #VALUE!
7 30-Nov-07 23:47 02:00 #VALUE!
8 30-Nov-07 23:47 03:00 #VALUE!
9 30-Nov-07 23:48 04:00 #VALUE!
10 30-Nov-07 23:48 05:00 #VALUE!
11 30-Nov-07 23:49 06:00 #VALUE!
12 30-Nov-07 23:50 07:00 #VALUE!
13 30-Nov-07 23:51 08:00 #VALUE!
14 30-Nov-07 23:52 09:00 #VALUE!
15 30-Nov-07 23:53 10:00 #VALUE!
16 30-Nov-07 23:55 11:00 #VALUE!
17 30-Nov-07 23:55 12:00 #VALUE!
18 30-Nov-07 23:57 13:00 #VALUE!
19 30-Nov-07 23:57 14:00 #VALUE!
20 30-Nov-07 23:58 15:00 #VALUE!
21 1-Dec-07 00:02 16:00 #VALUE!
22 1-Dec-07 00:02 17:00 #VALUE!
23 1-Dec-07 00:03 18:00 #VALUE!
24 1-Dec-07 00:04 19:00 #VALUE!
25 1-Dec-07 00:04 20:00 #VALUE!
26 1-Dec-07 00:04 21:00 #VALUE!
27 1-Dec-07 00:04 22:00 #VALUE!
28 1-Dec-07 00:06 23:00 #VALUE!
29 1-Dec-07 00:06 00:00 #VALUE!
30 1-Dec-07 00:07 01:00 #VALUE!
31 1-Dec-07 00:07 02:00 #VALUE!
32 1-Dec-07 00:07 03:00 #VALUE!


I need to read up on the SUMPRODUCT function. I was trying a similar approach using the COUNTIF but, obviously, not getting the count result I am looking for.

This counting scenario seems so simple but when you throw in the date/time values, it must need some additional argument formatting that escapes me at the moment.

Robert Chudek

I think G2 needs to be a date and time (in the one cell) as per my example, otherwise you won't get the correct results. Nevertheless that shouldn't cause a #VALUE! error.

What do you get if you just try

=D2+E2

This should give a number (as it should if copied down to row 600). If you have any text in D2:E600 you'll get an error

In cell F2 I put the formula =d$2+e$2 and copied it down.

When column F is formated as "General", I get the concatenated date & time. Changing the cell format to a 7 decimal place number I get the value: 39416.9881944. This is promising.

Then I simplified your formula using this new concatenated F column:

=SUMPRODUCT((F$2:F$600>=G2)*(F$2:F$600< G2+"1:00"))

It still returns the #VALUE! result.

You suggested column G should be a date + time format. I summed the G cell and date cell D. I updated the formula to:

=SUMPRODUCT((F$2:F$600>=(D2+G2))*(F$2:F$600+E$2:E$600< (D2+G2+"1:00")))

That formula started returning numbers. However they are not the correct numbers! I am going to post this progress report and continue researching my problem.

If anyone sees the answer, please let me know!