Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
Somebody asked me, a real Excel doofus, how to set up an array formula to
evaluate a column of dates. She wants to look at the dates in A2:A20, for example, and get a count of how many fall within a specified date range, e.g., a calendar month. I tried a couple things with zero success. Who can help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
=SUMPROUCT(--(MONTH($A$2:$A$200)=1),--(YEAR($A$2:$A$200)=2008))
counts how many in Jan 2008 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LarryP" wrote in message ... Somebody asked me, a real Excel doofus, how to set up an array formula to evaluate a column of dates. She wants to look at the dates in A2:A20, for example, and get a count of how many fall within a specified date range, e.g., a calendar month. I tried a couple things with zero success. Who can help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
=sumproduct(--text(a1:a20,"yyyymm")="200804")
is one way. You could use a couple of =countif()'s, too. =countif(a1:a20,"="&date(2008,4,1)) - countif(a1:a20,"="&date(2008,5,1)) (Make sure you format the cell with the formula as General or number--not date.) You could use the whole column with =countif()'s. You can only use the whole column with =sumproduct() in xl2007. LarryP wrote: Somebody asked me, a real Excel doofus, how to set up an array formula to evaluate a column of dates. She wants to look at the dates in A2:A20, for example, and get a count of how many fall within a specified date range, e.g., a calendar month. I tried a couple things with zero success. Who can help? -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
Hi,
Maybe this might help.. =COUNTIF(A2:A20,"=01/01/2008")-COUNTIF(A2:A20,"=01/02/2008") This will return the number of dates for the range in January 2008 The dates in the formula are in DD/MM/YYYY format. Hope this helps Gav. "LarryP" wrote: Somebody asked me, a real Excel doofus, how to set up an array formula to evaluate a column of dates. She wants to look at the dates in A2:A20, for example, and get a count of how many fall within a specified date range, e.g., a calendar month. I tried a couple things with zero success. Who can help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
Thanks to Bob and all. Will have her try these.
"Bob Phillips" wrote: =SUMPROUCT(--(MONTH($A$2:$A$200)=1),--(YEAR($A$2:$A$200)=2008)) counts how many in Jan 2008 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LarryP" wrote in message ... Somebody asked me, a real Excel doofus, how to set up an array formula to evaluate a column of dates. She wants to look at the dates in A2:A20, for example, and get a count of how many fall within a specified date range, e.g., a calendar month. I tried a couple things with zero success. Who can help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
On Tue, 15 Apr 2008 06:31:01 -0700, Gav123
wrote: Hi, Maybe this might help.. =COUNTIF(A2:A20,"=01/01/2008")-COUNTIF(A2:A20,"=01/02/2008") This will return the number of dates for the range in January 2008 The dates in the formula are in DD/MM/YYYY format. Hope this helps Gav. That formula will only work, as posted, if the OP's Windows Regional settings are also in dd/mm/yyyy format. Much better would be to use an unambiguous method of representing the date. Either enter the date in cells, and refer to the cells, or use the DATE function. e.g. With start date in A1 and end date in A2: =COUNTIF(B:B,"="&A1)-COUNTIF(B:B,""&A2) or =COUNTIF(B:B,"="&DATE(2008,1,1))-COUNTIF(B:B,""&DATE(2008,1,31)) With any date in a month in A1, to get all the dates in that month: =COUNTIF(B:B,"="&A1-DAY(A1)+1)-COUNTIF(B:B,""&A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function to Evaluate Dates
Thanks Ron..
Everyday there is an opportunity to learn something new.. Gav. "Ron Rosenfeld" wrote: On Tue, 15 Apr 2008 06:31:01 -0700, Gav123 wrote: Hi, Maybe this might help.. =COUNTIF(A2:A20,"=01/01/2008")-COUNTIF(A2:A20,"=01/02/2008") This will return the number of dates for the range in January 2008 The dates in the formula are in DD/MM/YYYY format. Hope this helps Gav. That formula will only work, as posted, if the OP's Windows Regional settings are also in dd/mm/yyyy format. Much better would be to use an unambiguous method of representing the date. Either enter the date in cells, and refer to the cells, or use the DATE function. e.g. With start date in A1 and end date in A2: =COUNTIF(B:B,"="&A1)-COUNTIF(B:B,""&A2) or =COUNTIF(B:B,"="&DATE(2008,1,1))-COUNTIF(B:B,""&DATE(2008,1,31)) With any date in a month in A1, to get all the dates in that month: =COUNTIF(B:B,"="&A1-DAY(A1)+1)-COUNTIF(B:B,""&A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluate function | Excel Worksheet Functions | |||
Function evaluate multiple cells and return 1st one w/a value | Excel Discussion (Misc queries) | |||
Evaluate text string as a function | Excel Discussion (Misc queries) | |||
Using AND function to evaluate cells | Excel Worksheet Functions | |||
EVALUATE Function | New Users to Excel |