CountIf on Date / Time
 

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.

CountIf on Date / Time
 

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

"RoadKill" wrote in message
...
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.

CountIf on Date / Time
 

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))

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
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.

CountIf on Date / Time
 

Give this a try...

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

--
Rick (MVP - Excel)


"RoadKill" wrote in message
...
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.

CountIf on Date / Time
 

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.

"RoadKill" wrote:

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.

CountIf on Date / Time
 

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.

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
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.

"RoadKill" wrote:

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.

CountIf on Date / Time
 

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

CountIf on Date / Time
 

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))


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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

CountIf on Date / Time
 

"T. Valko" wrote...
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))
....

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))

CountIf on Date / Time
 

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

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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))
...

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))

CountIf on Date / Time
 

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" wrote in message
...
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))


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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

CountIf on Date / Time
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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" wrote in message
...
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))


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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