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

Jim


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.

Aladin

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

Jim,

Snippet is underway back to you.

Aladin