Can anyone advise how to count the number of cells containing dates in the
range A1:A1000. Is there a condition I can use in COUNTIF?

If it helps, the dates are customer formatted as dd.mm.yy

Many thanks,

Jason

That is a bit difficult as a date is just a number, so testing the value 1
would say that is a valid date.

This formula counts the cells A1:A10 that have numbers that equate to dates
between 1st Jan 2000 and today. Adjust to suit

=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(--"2000-01-01"&":"&TODAY())),A1:A10,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"msnews.microsoft.com" wrote in message
...
Can anyone advise how to count the number of cells containing dates in the
range A1:A1000. Is there a condition I can use in COUNTIF?

If it helps, the dates are customer formatted as dd.mm.yy

Many thanks,

Jason

Hi,

You could just use something like...

=COUNTIF(A1:A100,"01/01/1920")

Hope this helps,

Gav.

"msnews.microsoft.com" wrote:

Can anyone advise how to count the number of cells containing dates in the
range A1:A1000. Is there a condition I can use in COUNTIF?

If it helps, the dates are customer formatted as dd.mm.yy

Many thanks,

Jason

Excel uses numbers to represent dates. Jan. 1, 1900 is day 1, Jan 2, 1900 is
day 2. Jan 1, 1950 is day 18,264. Jan 1 2008 is day 39,448. Today, Feb 1,
2008 is day 39,479. A fast way to see the number for a date is to enter the
date in a cell then press Ctrl+Accent Grave - the leftmost key in the top
row. Press Ctrl+Accent Grave again to return to normal. Excel uses the
numbers 1 (Jan 1, 1900) through 2,958,465 (Dec. 31, 9999) to represent
dates. So you can see the problem. Is a number a number or a date? It
depends on how you interpret it. You can use those numbers for calculations
or format them as dates.

Tyro

"msnews.microsoft.com" wrote in message
...
Can anyone advise how to count the number of cells containing dates in the
range A1:A1000. Is there a condition I can use in COUNTIF?

If it helps, the dates are customer formatted as dd.mm.yy

Many thanks,

Jason