Counting nubmer of months with correct year..

[Arpa]

Hi everyone,

I have a range of cells A1:A10, lets say, that contain
delivery date for some product. These date can vary from
say January to December of 2004 to 2008. The format is"
MM/DD/YYYY).

I like to write a formula that counts how many delivery
dates is scheduled this month for this year.
So the idea is that in Cell B1, I will type today's date.

Then I like the formula to tell me how many delivery is
scheduled (from the range A1:A10) that has this month and
this year typed-in.

I tried several things but without success. Any help
please?

Thanks
Arpa.

 

[Domenic]

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(B1)),--(YEAR(A1:A10)=YEAR(B1)))

Hope this helps!

 

[Arpa]

Hi Dominic,

I tried this but it doesn't appear to work. It gives me
zero as a answer. I've tested it with typing in the date
that is exactly the same as cell B1 and it still gives me
zero as an answer.
Just trying to figure out if =Month(A1:A10)=Month(B1)
works, I only typed this formula and i got a #Value type
error !!

Any idea please?

Thanks

 

[Ron Rosenfeld]

Hi everyone,

I have a range of cells A1:A10, lets say, that contain
delivery date for some product. These date can vary from
say January to December of 2004 to 2008. The format is"
MM/DD/YYYY).

I like to write a formula that counts how many delivery
dates is scheduled this month for this year.
So the idea is that in Cell B1, I will type today's date.

Then I like the formula to tell me how many delivery is
scheduled (from the range A1:A10) that has this month and
this year typed-in.

I tried several things but without success. Any help
please?

Thanks
Arpa.

With a date, named Dt, that represents a date in the month of interest, or a
reference to some cell that contatins a Date in the month of interest:

=COUNTIF(A1:A10,">="&DATE(YEAR(Dt),MONTH(Dt),1)) -
COUNTIF(A1:A10,">"&DATE(YEAR(Dt),MONTH(Dt)+1,0))


--ron

 

[Peo Sjoblom]

Looks like your dates are text instead of numeric values, select an empty
cell,
copy it, select the dates, do edit>paste special and check add.
Now try the formula

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)