A very easy question

[niallmulhare]

He I'm hoping someone might be able to help me on this one.

I have a column which contains date and time as follows.

11/25/2006 10:44:00 AM
10/26/2006 12:44:00 AM
12/24/2006 13:44:00 AM
12/25/2006 14:44:00 AM
14/24/2006 15:44:00 AM
10/23/2006 12:44:00 AM
10/15/2006 16:44:00 AM

Wht I'm looking to do it have a query/function that will return all the date's that are the same regardless of the time.

What Kind of query could I use that would return all the dates that are e.g. 10/15/2006 EXCLUDING the time? Is this possible?

Ive tried the following but this only returns 0

=COUNTIF(C2:C100,"11/25/2006")

and e.g. =COUNTIF(C2:C100,"10/25/2006*")

But neither works,

any suggestions would be great.

 

[texasalynn]

you have invalid dates and times is one issue

 

[ExcelChampion]

Try something along the lines of:-

=SUMPRODUCT(--(DATE(YEAR(A1:A7),MONTH(A1:A7),DAY(A1:A7))=DATEVALUE("11/25/2006")))

FYI:- you have invalid dates in your example...

HTH!

 

[lenze]

Hi and welcome to theboard. Why not parse the columns into 2 seperate ones. Use Data>Text to Columns. Now a simple Pivot Table will do the trick.

HTH

lenze

 

[niallmulhare]

Hey thanks for the info, you were all very helpful.
I got it working with the :=SUMPRODUCT(--(DATE(YEAR(C2:C100),MONTH(C2:C100),DAY(C2:C100))=DATEVALUE("10/19/2006")))

and am going to look into the pivot table approach, but unfortunately I dont know much about pivot tables.


Thanks again.