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)<=MONTH(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.

=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)<=MONTH(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, 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)<=MONTH(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
>

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