Ignoring Time Value from DateTime cells - MS Excel

Question

I have following date values (with time) in Column A.

1/1/2012 8:50  
1/1/2012 8:45  
1/1/2012 8:55  
1/1/2012 8:59  
1/1/2012 8:12  
1/1/2012 8:30  
1/1/2012 9:50  
1/1/2012 10:00

And the following value in Cell B1.

1/1/2012

Now, when I apply formula =COUNTIF(A:A,"=" & B1) to count the number of cells in Column A matching Cell B1, it gives me zero as it is also considering the Time Value.

Any workaround/trick for this?

Answer 1

INT function will take just date from date/time so try SUMPRODUCT like this

=SUMPRODUCT((INT(A2:A10)=B1)+0)

....and better to restrict the range as I have so you don't use the whole column with SUMPRODUCT (mandatory in Excel 2003 or earlier)

...or using COUNTIFS check that the date/time is between B1 and B1+1 (some time in the date B1), i.e.

=COUNTIFS(A:A,">="&B1,A:A,"<"&B1+1)