A very easy question

niallmulhare

New Member
Joined
Oct 25, 2006
Messages
2
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Oct 25, 2006
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,388
Messages
5,547,661
Members
410,805
Latest member
Ginoji
Top