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

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" wrote in message
...
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, this formula returns a #value? but I used the serial number in my
countif and that works for me.
Thank you!

"Bob Umlas" wrote:

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" wrote in message
...
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

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


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Caroline" wrote:

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

"Bob Umlas" wrote:

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" wrote in message
...
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