Count cells with a date in them

[Caroline]

Hello,
I would like to count cells in a column that have dates in them -any date, I
do not want to count cells with text or anything that's not a date.
I'm trying to use a countif, but I don't know how to write the criteria of a
date.
Thank you,
Caroline

 

[Bob Umlas]

a date to excel is a serial number, FORMATTED as a date. You can try
something like this:
=SUMPRODUCT(N(YEAR(A1:A100)>=2001),N(YEAR(A1:A100)<=2010))
to count the number of values which fall between 2001 and 2010, but a value
of 39868, for example, will be counted, since that's the serial# for today.
HTH - Bob Umlas - Excel MVP

 

[Caroline]

Bob, this formula returns a #value? but I used the serial number in my
countif and that works for me.
Thank you!

 

[Shane Devenshire]

Remember, as Bob was pointing out, no Excel spreadsheet function can tell if
a cell is a date or a number if that number is between 0 and 2,958,465 - the
date range supported by Excel.

If you column only has dates, blanks, or text, no numbers then

=COUNTIF(A1:A100,">=0")

You can write your own VBA function to do this:

Function CountDates(R As Range)
For Each cell In R
If IsDate(cell) Then
Total = Total + 1
End If
Next cell
CountDates = Total
End Function