Counting columns of dates
 

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


--
mango7
------------------------------------------------------------------------
mango7's Profile: http://www.excelforum.com/member.php...o&userid=29529
View this thread: http://www.excelforum.com/showthread...hreadid=492293

Counting columns of dates
 

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



"mango7" wrote:


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


--
mango7
------------------------------------------------------------------------
mango7's Profile: http://www.excelforum.com/member.php...o&userid=29529
View this thread: http://www.excelforum.com/showthread...hreadid=492293

Counting columns of dates
 

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.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=492293

Counting columns of dates
 

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


--
mango7
------------------------------------------------------------------------
mango7's Profile: http://www.excelforum.com/member.php...o&userid=29529
View this thread: http://www.excelforum.com/showthread...hreadid=492293

Counting columns of dates
 

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

"mango7" wrote in
message ...

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


--
mango7
------------------------------------------------------------------------
mango7's Profile:
http://www.excelforum.com/member.php...o&userid=29529
View this thread: http://www.excelforum.com/showthread...hreadid=492293