counting cells within certain time frames - 24 hour clock
 

I am looking to count the number of cells that meet criteria in a 24 hour
clock format.

The cell range is C2:C1395.
If a time falls between 00:00:00 and 00:59:59, I want to count it. This
would continue for the remaining 23 hours

I was able to get this to work at one point using COUNTIF, but now it is not
functioning and an error keeps appearing. Also, would it make a difference in
the formula if I was using Excel 03 versus 07?

Thanks for any help.

counting cells within certain time frames - 24 hour clock
 

Times are stored by Excel as fractions of a 24-hour day, so 1 hour is
actually 1/24, 2 hours is 2/24 etc.

If you put 1 to 24 in (for example) E2:E25, then you can start with
this in F2:

=COUNTIF(C$2:C$1395,"<"&E2/24) - COUNTIF(C$2:C$1395,"<"&E1/24)

assuming E1 is blank. Then just copy this down to F25, where the
numbers in column E represent the hours that you are counting up to
(excluding them).

Hope this helps.

Pete

On May 5, 4:55*pm, lummox wrote:
I am looking to count the number of cells that meet criteria in a 24 hour
clock format.

The cell range is C2:C1395.
If a time falls between 00:00:00 and 00:59:59, I want to count it. This
would continue for the remaining 23 hours

I was able to get this to work at one point using COUNTIF, but now it is not
functioning and an error keeps appearing. Also, would it make a difference in
the formula if I was using Excel 03 versus 07?

Thanks for any help.

counting cells within certain time frames - 24 hour clock
 

Try these. Assuming no empty cells.

12:00:00 AM to 12:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)=0),--(HOUR(A1:A10)<1))

1:00:00 AM to 1:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)=1),--(HOUR(A1:A10)<2))

Follow that pattern until you get to

23:00:00 to 23:59:59

=SUMPRODUCT(--(HOUR(A1:A10)=23),--(A1:A10<1))

--
Biff
Microsoft Excel MVP


"lummox" wrote in message
...
I am looking to count the number of cells that meet criteria in a 24 hour
clock format.

The cell range is C2:C1395.
If a time falls between 00:00:00 and 00:59:59, I want to count it. This
would continue for the remaining 23 hours

I was able to get this to work at one point using COUNTIF, but now it is
not
functioning and an error keeps appearing. Also, would it make a difference
in
the formula if I was using Excel 03 versus 07?

Thanks for any help.

counting cells within certain time frames - 24 hour clock
 

No luck on either of the suggestions. Any other options?


"T. Valko" wrote:

Try these. Assuming no empty cells.

12:00:00 AM to 12:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)=0),--(HOUR(A1:A10)<1))

1:00:00 AM to 1:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)=1),--(HOUR(A1:A10)<2))

Follow that pattern until you get to

23:00:00 to 23:59:59

=SUMPRODUCT(--(HOUR(A1:A10)=23),--(A1:A10<1))

--
Biff
Microsoft Excel MVP


"lummox" wrote in message
...
I am looking to count the number of cells that meet criteria in a 24 hour
clock format.

The cell range is C2:C1395.
If a time falls between 00:00:00 and 00:59:59, I want to count it. This
would continue for the remaining 23 hours

I was able to get this to work at one point using COUNTIF, but now it is
not
functioning and an error keeps appearing. Also, would it make a difference
in
the formula if I was using Excel 03 versus 07?

Thanks for any help.

counting cells within certain time frames - 24 hour clock
 

Are you sure your times are true Excel times? Do the entries also contain
dates, like this 1/1/2009 22:15:00 ? In Excel, time is really a number, the
fractional part of a day (which has a value of 1), formatted to look like a
time entry. 12:00 PM is half a day so half of 1 is 0.5. The true value of
12:00 PM is 0.5.

You can test to see if the entries are true time values by using one of
these formulas:

=COUNT(A1)
=ISNUMBER(A1)

If the entry in A1 is a true Excel time value COUNT will return 1 and
ISNUMBER will return TRUE.

--
Biff
Microsoft Excel MVP


"lummox" wrote in message
...
No luck on either of the suggestions. Any other options?


"T. Valko" wrote:

Try these. Assuming no empty cells.

12:00:00 AM to 12:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)=0),--(HOUR(A1:A10)<1))

1:00:00 AM to 1:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)=1),--(HOUR(A1:A10)<2))

Follow that pattern until you get to

23:00:00 to 23:59:59

=SUMPRODUCT(--(HOUR(A1:A10)=23),--(A1:A10<1))

--
Biff
Microsoft Excel MVP


"lummox" wrote in message
...
I am looking to count the number of cells that meet criteria in a 24
hour
clock format.

The cell range is C2:C1395.
If a time falls between 00:00:00 and 00:59:59, I want to count it. This
would continue for the remaining 23 hours

I was able to get this to work at one point using COUNTIF, but now it
is
not
functioning and an error keeps appearing. Also, would it make a
difference
in
the formula if I was using Excel 03 versus 07?

Thanks for any help.