MrExcel Publishing
Your One Stop for Excel Tips & Solutions

TIME - Dealing with it -COUNTIF

Posted by Jim Q on February 03, 2001 5:49 AM

One column has a time of day in it, i.e 9:45 AM, non date specific. I want
to create a countif that will count the number of times the 'time' falls
into a particular period. i.e. 9:00 to 10:00. I am having nothing but
problems with this. The main problem is getting the time recognized and
dealt with

All help is greatly appreciated


Posted by Aladin Akyurek on February 03, 2001 7:53 AM

I assume that you have time values in A, entered as follows:
9:10 AM
9:45 AM
9:55 AM
10:30 AM

They need not to be sorted.

Enter in B1 e.g., 9:45 AM and in C1 e.g., 10:00 AM

Then array-enter (that is, hit control+shift+enter at the same time after typing the formula).

D1 =SUM((A2:A4>=B1)*(A2:A4<=C1))

If in doubt, send me a snippet of your time values.


Posted by Aladin Akyurek on February 03, 2001 5:01 PM


Snippet is underway back to you.