Counting dates for a the present month but not future months
Dave, Thanks for your reply. Your method is much more compact than the one I
was useing.
However, I'm still having problems only returning a number for dates less
than or equal to the current date. For example, if my list of dates had
November, December and January dates, and it was currently December, I only
want to count November and December dates in thier respective cells and the
January cell would be forced to 0 regardless of how many January dates are in
the list.
Here's what I tried to do:
=IF(MONTH(my_date) <=
MONTH(NOW()),SUMPRODUCT(--(TEXT(my_date,"mmm-yy")="Jan-06")), 0)
Thanks!!
"Dave Peterson" wrote:
=sumproduct(--(text(a1:a10,"yyyymm")="200512")
is one way to count the dates in December of 2005.
BrianInCalifornia wrote:
How do I count the number of dates found in an array of dates, for a given
month. In addition, I do not wish to count any dates that occur in future
months.
Right now my formula looks like this, where "my_dates" is a range on another
worksheet containing dates, and "given_mo_yr" is a given Month/Year date.
{COUNT(IF(YEAR(my_date)=YEAR(given_mo_yr),IF(MONTH (my_date)=MONTH(given_mo_yr),IF(MONTH(my_date)<=MO NTH(NOW()),my_date))))}
The problem with this is that it counts dates that occur in the future, and
my goal is to not count those dates until it is that month.
--
Dave Peterson
|