CountIf on Date / Time

[RoadKill]

Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

 

[Ashish Mathur]

Hi,

Try this

=SUMPRODUCT((((VALUE(C5:C7)-(INT(VALUE(C5:C7)))>=0.5))*(VALUE(C5:C7)-(INT(VALUE(C5:C7)))<=0.540972222224809)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

My date/time field looks like this: 5/6/2009 12:01:00 AM.

If the entries are true Excel date/time values *and* there are no empty
cells within the range:

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

 

[Rick Rothstein]

Give this a try...

=SUMPRODUCT(--(MOD(A7:A19,1)<0.041666))

 

[RoadKill]

Interesting that all three approaches gave vastly different results, with
none of them being correct. I thank you all for the help but will go a
different route.

 

[T. Valko]

If you can provide more detail maybe we can figure out what the problem is.
My first guess would be that your entries aren't true Excel date/times but
are instead TEXT strings.

 

[Thomas M.]

Excel 2007

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

I have a similar need as the original poster, except that I need to count up
entries by month. I modified the above command as follows:

=SUMPRODUCT(--(MONTH(Incident_Dates)=4))

That works to count all the entries for April. However, my Incident_Dates
range is 100 rows, and most of those do not yet contain data. If I use my
version of your formula and the range does not contain any dates for a given
month, the formula returns 100 minus the number of cells in the range that
do contain data. How could the number of entries for a given month be
counted without the blank cells skewing the results?

--Tom

 

[T. Valko]

Empty cells will evaluate as month 1 (January). So, if you're counting for
month 4 (April) the empty cells shouldn't make a difference. The only time
the empty cells will make a difference is if you're counting month 1.

To account for that just add a test that the cells contain a number or that
the cells are not blank.

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=4))

=SUMPRODUCT(--(Incident_Dates<>""),--(MONTH(Incident_Dates)=4))

 

[Harlan Grove]

If the entries are true Excel date/time values *and* there are no empty
cells within the range:

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

....

So filter out the empty cells. BTW, HOUR returns the hour for text
that could be converted by TIMEVALUE, so it's only blank cells that
would be trouble.

Anyway, try

=SUMPRODUCT(--(TEXT(A2:A10000,"h;;;")="0"))

or

=SUMPRODUCT(--(HOUR(A2:A10000)-ISBLANK(A2:A10000)=0))

 

[Thomas M.]

So filter out the empty cells.

Yep. Just didn't know how to do that from a syntax point of view as I am
not very familiar with the SUMPRODUCT function. I'll give your suggestions
a try. Thanks for the help.

--Tom

 

[Thomas M.]

I used the first option and it that works perfectly.

In my particular situation I need to keep a counter for each month. So I
have the months listed in F1:Q1, I named that range, and then in F2:Q2 I
have the following modification of your formula:

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=MATCH(I1,Months_List,0)))

That gives me a formula that is the same for all 12 months (I don't have to
put =1, =2, =3, etc. at the end of each formula).

Thanks for the help.

--Tom

 

[T. Valko]

You're welcome. Thanks for the feedback!