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?

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

Solution 2

this is working better:

 =DATEVALUE(TEXT(A1,"dd/mm/yyyy"))

Solution 3

I figured out a workaround. Create an additional column for INT of column A and INT of column B and then use the match function against those columns. Kind of clunky but it works.

        A       ||      B     ||      C     ||     D     ||        E
 --------------------------------------------------------------------------
 1/1/2012 8:50  ||  1/1/2012  ||  =INT(A2)  ||  =INT(B2) ||  =MATCH(C2, D2)

Comments

[Jacob Bolda]

To add to barry's answer, a date in excel is just a number incremented from some arbitrary day in the past. For example, type 2/5/2013 into any cell. Excel will automatically format this cell as a date. Now right click, and format the cell. Under the number tab, select general and you will see excel really has the date stored as 41310. Now excel does the same thing for time, except that time is in fractions of a day (or decimals). This is why the INT() function works well to just get the date. It rounds the time to the nearest day (or rounds the decimal to the nearest whole number).

[teylyn]

Int() does NOT round to the nearest whole number. It simply cuts off the decimal.

[Jacob Bolda]

I forgot the type down. It rounds the decimal down to the nearest whole number.

[TechGeek]

@BarryHoudini: Can you pls have a look at stackoverflow.com/questions/14747689/…