counting instances of specific times in fields with date and time
 

I have a column containing date and time, for example:

10/13/2006 7:30:00 PM

I'm trying to count the instances of specific times (i.e. how many 7:30 pm's
or 3:00 pm's).

Tried COUNTIF(D:D,"7:00:00PM"), but that doesn't work, I'm guessing because
the date and time are all one numeric string in Excel ... but I don't know
how to search and count just the time portion.

Any help appreciated!

counting instances of specific times in fields with date and time
 

Try something like this:

With a list of date/time values in A1:A10

B1: =SUMPRODUCT(--(ROUND(MOD(A1:A10,1),6)=ROUND(--"7:30:00 PM",6)))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Rob Odum" wrote:

I have a column containing date and time, for example:

10/13/2006 7:30:00 PM

I'm trying to count the instances of specific times (i.e. how many 7:30 pm's
or 3:00 pm's).

Tried COUNTIF(D:D,"7:00:00PM"), but that doesn't work, I'm guessing because
the date and time are all one numeric string in Excel ... but I don't know
how to search and count just the time portion.

Any help appreciated!

counting instances of specific times in fields with date and t
 

Like a charm ... thanks!!!!

"Ron Coderre" wrote:

Try something like this:

With a list of date/time values in A1:A10

B1: =SUMPRODUCT(--(ROUND(MOD(A1:A10,1),6)=ROUND(--"7:30:00 PM",6)))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Rob Odum" wrote:

I have a column containing date and time, for example:

10/13/2006 7:30:00 PM

I'm trying to count the instances of specific times (i.e. how many 7:30 pm's
or 3:00 pm's).

Tried COUNTIF(D:D,"7:00:00PM"), but that doesn't work, I'm guessing because
the date and time are all one numeric string in Excel ... but I don't know
how to search and count just the time portion.

Any help appreciated!