Counting Dates

M

MJMP

In a column, there are entries with dates (in MM/DD/YYYY format). How can I
count the number of cells that have dates in them? I do not want to input a
criteria for the date. I just would like the number of cells that have any
date in it.

Thanks!
 
B

Bob Phillips

Tricky, because dates in Excel are just number of days since 01//01/1900, so
it is difficult to distinguish a date from any other positive integer number

If you set limits on the dates, say since 1970, and up until 2010, you could
use

=SUMPRODUCT(--(A1:A1000>=--"1970-01-01"),--(A1:A1000<=--"2011-01-01"))
 
P

Peo Sjoblom

You can't really check if it is a date since dates are plain numbers
formatted as dates with date 0 on jAN 0 1900, however you can check for
numbers in the range
(there is a function called CELL("format",cell) but it is not reliable since
if the format changes it will change as well

=SUMPRODUCT(--(ISNUMBER(Range))

will count cells with numbers, if they are text you can use
ISTEXT instead of ISNUMBER

Regards,

Peo Sjoblom
 
Top