Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Dates?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Dates?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Dates?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Dates?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT IF BETWEEN DATES | Excel Worksheet Functions | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
count dates | New Users to Excel | |||
How do a count dates? | Excel Discussion (Misc queries) | |||
How do a count dates? | Excel Discussion (Misc queries) |