Array Function to Evaluate Dates

  • Thread starter Thread starter LarryP
  • Start date Start date
L

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?
 
=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)
 
=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.
 
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.
 
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.
Click to expand...

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
 
Back
Top