Counting columns of dates

M

mango7

I want to create a column which will give me a total of the number o
columns which contain a date. Therefore count the columns not the dat
in the cells. Any ideas
 
C

CLR

If all of your "data" is TEXT and/or numbers below 30000, then this might do
it for you..........

=COUNTIF(A3:F3,">30000")

Vaya con Dios,
Chuck, CABGx3
 
V

Vito

Because Excel translates dates to numbers, this is a difficult task.

You can try something like:
=SUMPRODUCT(--(A1:D1>=DATE(2005,1,1)),--(A1:D1<=DATE(2005,12,31)))

you can change the dates to what would be the earliest possible date
and the latest possible date. Note that the dates are translated as 5
digit numbers. So Jan 1, 2005 is equivalent to 38353 and Dec 1, 2005 is
38691 so as long as you don't have numeric values within that range, the
formula will count dates only.
 
M

mango7

thanks
my next problem is keeping the formula when I add new rows. I
understand that using the list feature may help but I can't find the
list feature. I'm using 2002
 
B

Biff

Hi!

Try this:

Assuming the range in question is B1:J10.

Dates are really just numbers formatted to look like a date, so in essence
you just have to "test" for the presence of a number in your columns. This
could lead to incorrect results if you have other numeric values in the
columns that are not dates:

=SUMPRODUCT(--(COUNTIF(OFFSET(B1:B10,,COLUMN(B1:J1)-COLUMN(B1)),">0")>0))

Biff
 
Top