Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates for a the present month but not future months | Excel Worksheet Functions | |||
counting dates | New Users to Excel | |||
How to Calculate Dates without counting the weekends | Excel Worksheet Functions | |||
counting in one column when two expressions in two other columns are true | Excel Worksheet Functions | |||
counting entries between two dates? | Excel Worksheet Functions |