Calculating Half-Hour Range for Hour-Minute Data

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.
 
B

Bernie Deitrick

Anon,

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
 
A

Anonymous

Thanks very much, Bernie. I'll give this a try.

Where you listed A:A below were you implying that the
actual formula would be A1:A999 depending on the actual
number of rows I have?

Also does the source time column need to be in a specific
time format?

Thanks!!!
 
A

Anonymous

Bernie,

I also found some good information on the web that
suggests using FLOOR, CEILING, and MROUND functions to
round the time value, which might be a good solution, too.

Thanks again.
 
B

Bernie Deitrick

Anon,

As long as only your times are in column A, you can refer to the entire
column.

It helps you if all the times are in time format, but it isn't a
prerequisite.

HTH,
Bernie
MS Excel MVP
 
Top