counting instances of specific times in fields with date and time

R

Rob Odum

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!
 
R

Ron Coderre

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
 
R

Rob Odum

Like a charm ... thanks!!!!

Ron Coderre said:
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
Click to expand...
 
Top