Array Function to Evaluate Dates

[LarryP]

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?

 

[Bob Phillips]

=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)

 

[Dave Peterson]

=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.

 

[Gav123]

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.

 

[Ron Rosenfeld]

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

 

[Gav123]

Thanks Ron..

Everyday there is an opportunity to learn something new..

Gav.