countif on unformatted dates
 

I have few dates in column A but it starts with an apostrophe sign.
Lets say the range is A1:A9 and here is a sample data
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/7/2006 11:35:08 AM
'6/8/2006 11:35:08 AM
'6/9/2006 11:35:08 AM
'6/9/2006 11:35:08 AM

I have the following values in cell A11:A15
6/5/2006
6/6/2006
6/7/2006
6/8/2006
6/9/2006

If I do a countif in cell B11 =COUNTIF(A1:A9,A11), the answer I get is
ZERO

How come???

I know it has got something to do with formats but not sure what. What
would be the correct formula?

countif on unformatted dates
 

wrote in message
ups.com...
I have few dates in column A but it starts with an apostrophe sign.
Lets say the range is A1:A9 and here is a sample data
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/7/2006 11:35:08 AM
'6/8/2006 11:35:08 AM
'6/9/2006 11:35:08 AM
'6/9/2006 11:35:08 AM

I have the following values in cell A11:A15
6/5/2006
6/6/2006
6/7/2006
6/8/2006
6/9/2006

If I do a countif in cell B11 =COUNTIF(A1:A9,A11), the answer I get is
ZERO

How come???

I know it has got something to do with formats but not sure what. What
would be the correct formula?

Your A1 to A9 are text values, forced to that because of the apostrophe.

Without the apostrophe you can hopefully format the cells as date & time.
--
David Biddulph

countif on unformatted dates
 

One possible way using your example


=SUMPRODUCT(--(INT(--SUBSTITUTE($A$1:$A$9,"'",""))=A11))

copy down will give

3
2
1
1
2


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


wrote in message
ups.com...
I have few dates in column A but it starts with an apostrophe sign.
Lets say the range is A1:A9 and here is a sample data
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/7/2006 11:35:08 AM
'6/8/2006 11:35:08 AM
'6/9/2006 11:35:08 AM
'6/9/2006 11:35:08 AM

I have the following values in cell A11:A15
6/5/2006
6/6/2006
6/7/2006
6/8/2006
6/9/2006

If I do a countif in cell B11 =COUNTIF(A1:A9,A11), the answer I get is
ZERO

How come???

I know it has got something to do with formats but not sure what. What
would be the correct formula?

countif on unformatted dates
 

Thank you very much

Peo Sjoblom wrote:
One possible way using your example
=SUMPRODUCT(--(INT(--SUBSTITUTE($A$1:$A$9,"'",""))=A11))