Count Dates?

[msnews.microsoft.com]

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

 

[Bob Phillips]

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)

 

[Gav123]

Hi,

You could just use something like...

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

Hope this helps,

Gav.

 

[Tyro]

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