Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec) | Excel Discussion (Misc queries) | |||
24 hour clock formula for elapsed time between 1215 and 1630 | Excel Worksheet Functions | |||
Counting time-frames | Excel Discussion (Misc queries) | |||
time diffrence with 24 hour clock | Excel Worksheet Functions | |||
Counting the number of cells within a certain hour. | Excel Discussion (Misc queries) |