COUNTIF < DATE with other data in column
 

Hi

I want to count the dates in a column that are less than today, so i am using:

=COUNTIF('AM Live'!H:H,"<="&TODAY())

But this returns the wrong value.
In column H is other data than dates and think this is causing the error.

At the moment it returns 88 but there are only 9 or so less than today???

Thanks

John

Dates are nothing more than numbers in Excel, so any cell
in col. H with a value of 38,456 (which equates to April
14, 2005) will be counted in your formula. You need to
somehow distinguish actual dates from ordinary numbers in
your column.

To learn more about date/time in XL, see:

http://www.cpearson.com/excel/datetime.htm#SerialDates

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi

I want to count the dates in a column that are less than
today, so i am using:

=COUNTIF('AM Live'!H:H,"<="&TODAY())

But this returns the wrong value.
In column H is other data than dates and think this is
causing the error.

At the moment it returns 88 but there are only 9 or so
less than today???

Thanks

John
.

I meant to say "with a value of 38,456 or less"

Jason

-----Original Message-----
Dates are nothing more than numbers in Excel, so any
cell
in col. H with a value of 38,456 (which equates to April
14, 2005) will be counted in your formula. You need to
somehow distinguish actual dates from ordinary numbers
in
your column.

To learn more about date/time in XL, see:

http://www.cpearson.com/excel/datetime.htm#SerialDates

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi

I want to count the dates in a column that are less
than
today, so i am using:

=COUNTIF('AM Live'!H:H,"<="&TODAY())

But this returns the wrong value.
In column H is other data than dates and think this is
causing the error.

At the moment it returns 88 but there are only 9 or so
less than today???

Thanks

John
.

.

one other thing that can be done is if you have a lowest date you are
interested in and none of the other numbers have are other higher than the
serial number of that date the following equaition can be used:
=countif(H:H,"<38456")-countif(H:H,"36526")
if you only interested in dates in the 21st century

"Jason Morin" wrote:

I meant to say "with a value of 38,456 or less"

Jason

-----Original Message-----
Dates are nothing more than numbers in Excel, so any
cell
in col. H with a value of 38,456 (which equates to April
14, 2005) will be counted in your formula. You need to
somehow distinguish actual dates from ordinary numbers
in
your column.

To learn more about date/time in XL, see:

http://www.cpearson.com/excel/datetime.htm#SerialDates

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi

I want to count the dates in a column that are less
than
today, so i am using:

=COUNTIF('AM Live'!H:H,"<="&TODAY())

But this returns the wrong value.
In column H is other data than dates and think this is
causing the error.

At the moment it returns 88 but there are only 9 or so
less than today???

Thanks

John
.

.