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?

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?

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

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?

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?

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

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