COUNTIF Question re: time
Posted by Patrick on December 31, 2001 3:40 PM
I'm trying to create a formula that will count the number of times a specific time period shows up in a list. However, the time period is a range. I'm dealing with Military time so for example I would like to know how many times any time between 0:00 and 0:59 shows up. I'm having difficulties so any help would be greatly appreciated.
Posted by Tom Urtis on January 01, 2002 3:53 AM
Two points are not clear from your question:
(1) Whether you are looking to count occurrences for times in minutes that are less than one hour, or for times in seconds that are less than one minute.
(2) You say "between 0:00 and 0:59" which would mean from and including 0:01, to and including 0:58. I'd guess you want to include those high & low criteria ranges.
Let's assume you are trying to count occurrances of times that are in minutes, less than one hour, and that you want to include 0:00 and 0:59 entries in your count. If you are entering your military times in the range A1:A50, then in B1 enter 0:00, and in C1 enter 0:59. Then in D1 enter the formula
=COUNTIF(A1:A50,">="&B1)-COUNTIF(A1:A50,">"&C1),
which will count how many cells meet your criteria.
Important, be sure you format B1, C1, and A1:A50 as hh:mm (or h:mm), and that *after* you enter the above formula in D1, you then format D1 as General or Number no decimal. Funny thing about that formula, it is not an array, but it tends to automatically format the cell in time format when entered, so format the cell after entering it to get it to work.
Hope this helps.
Tom Urtis
Posted by Aladin Akyurek on January 01, 2002 10:49 AM
Hi Tom -- Happy New Year. Agree with all of the above.
> Funny thing about that formula, it is not an array,
Of course not.
> but it tends to automatically format the cell in time format when entered,
Yep. Excel simply jumps to conclusion that any computation with time will result in time, I think. Not a bad conclusion.
> so format the cell after entering it to get it to work.
Posted by Tom Urtis on January 01, 2002 12:06 PM
Thanks Aladin, welcome back (nt)
