Calculating Half-Hour Range for Hour-Minute Data

  • Thread starter Thread starter Anonymous
  • Start date Start date
A

Anonymous

I am trying to automate the work to put a graph
together. Basically I have some issues reported in one
column and the time (in HH:MM format -- 9:00 AM) each
issue was reported in another column.

The problem is that I want a chart that displays how many
issues were reported in half hour increments (e.g., from
9:00 AM to 9:30 AM, etc.). Right now I am doing this
manually my creating another column with the half-hour
increments and basing my chart on this manually created
column. Basically if I had two values in this range in
my source column (9:15 AM and 9:22 AM) I then put the
value 2 in my manual column to represent the amount of
issues reported between 9:00 and 9:30. My chart uses the
column with "2" in it.

Ideally, I would just be able to use a formula to count
all values from the column that are in a particular half-
hour increment. Since COUNTIF and SUMIF only appear to
allow for one condition, I don't see how I can do this.
Does anyone have any suggestions for how to automatically
count how many values from a column fall within a half-
hour range?

Thanks.
 
Anon,

Please don't mulit-post - we read most of the groups anyway, and this just
makes for more work. Here is my reply to your other post:

With your times in column A, put the start time for each time segment
starting in C2, and continuing down the column.

Then in cell D2, use the formula

=COUNTIF(A:A,">="&C2)-COUNTIF(A:A,">="&C3)

and copy down to match your times in column C.

HTH,
Bernie
MS Excel MVP
 
Back
Top