Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number counts within Date Range
I have about 45 different dates ranging from March through October (this
year) in one column. I want to bounce off today's date (NOW()) to count how many of these items are <30 days old, 31-60 days old, 61-90 days old and 91+days old. -- SteveStats |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number counts within Date Range
You have a few options...
Explore these: 30 or less: =COUNTIF(A1:A300,"="&TODAY()-30) 31 thru 60: =INDEX(FREQUENCY(A1:A300,TODAY()-{61,31}),2) or =COUNTIF(A1:A300,"="&TODAY()-60)-COUNTIF(A1:A300,"="&TODAY()-30) or =SUM(COUNTIF(A1:A300,"="&TODAY()-{60,30})*{1,-1}) 61 thru 90: =INDEX(FREQUENCY(A1:A300,TODAY()-{91,61}),2) or =COUNTIF(A1:A300,"="&TODAY()-90)-COUNTIF(A1:A300,"="&TODAY()-60) or =SUM(COUNTIF(A1:A300,"="&TODAY()-{90,60})*{1,-1}) Over 90: =COUNTIF(A1:A300,"<"&TODAY()-90) Adjust range references to suit your situation. Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "SteveStats" wrote in message ... I have about 45 different dates ranging from March through October (this year) in one column. I want to bounce off today's date (NOW()) to count how many of these items are <30 days old, 31-60 days old, 61-90 days old and 91+days old. -- SteveStats |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number counts within Date Range
Assuming source dates (real dates) are within A2:A10 with no blank cells,
and tightened limits with no gaps <=30 days =SUMPRODUCT(--(TODAY()-A2:A10<=30)) 31-60 days =SUMPRODUCT((TODAY()-A2:A1030)*(TODAY()-A2:A10<=60)) 61-90 days =SUMPRODUCT((TODAY()-A2:A1060)*(TODAY()-A2:A10<=90)) 90 days =SUMPRODUCT(--(TODAY()-A2:A1090)) Adapt the dates range A2:A10 to suit your actual range -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveStats" wrote: I have about 45 different dates ranging from March through October (this year) in one column. I want to bounce off today's date (NOW()) to count how many of these items are <30 days old, 31-60 days old, 61-90 days old and 91+days old. -- SteveStats |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of observations within a date range? | Excel Worksheet Functions | |||
A macro that counts the number of times a file is opened | Excel Discussion (Misc queries) | |||
a function that counts the number of cells with information | Excel Worksheet Functions | |||
Excel formula that counts events after a certain date? | Excel Discussion (Misc queries) | |||
Number of Days in a Date Range | Excel Discussion (Misc queries) |