Counting dates for a the present month but not future months
 

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.

Counting dates for a the present month but not future months
 

=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

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

Counting dates for a the present month but not future months
 

I'm not sure how far back you go, but maybe...

=sumproduct(--(a1:a10=date(2005,11,01)),--(a1:a10<=today()))
(for through today)

Or through the end of the current month:
=SUMPRODUCT(--(A1:A10=DATE(2005,11,1)),
--(A1:A10<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)))

(the 0th of any month is the last day of the previous month)

BrianInCalifornia wrote:

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


--

Dave Peterson