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" <(E-Mail Removed)> wrote in message
news:BD9AC6F8-737C-4590-B8CC-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:BD9AC6F8-737C-4590-B8CC-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:BD9AC6F8-737C-4590-B8CC-(E-Mail Removed)...
> > > 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
> >
> >
> >