Date without true value

[Rubem]

Hi,

I have a table that lists the invoice dates. On a monthly basis, I need to
write a report of how many invoices were issue, let's say, in January. I
converted the date mm/dd/yy into just mm/yy, but I still have the true value
in the background and when I try to count how many invoices were issued in
January, I get "0".

Any suggestion?

Thank you for your help.

Rubem

 

[T. Valko]

See if this does what you want.

As long as the dates are true Excel dates then this should work. Let's
assume the dates are in the range A1:A10. To count the number of dates for
a specific month:

=SUMPRODUCT(--(MONTH(A1:A10)=n))

Where n = the month number of interest. 1 = January to 12 = December

Note that empty cells will evaluate as month 1 (January). To account for
that when counting for Janauary:

=SUMPRODUCT(--(A1:A10<>""),--(MONTH(A1:A10)=1))

 

[Otto Moehrbach]

Ruben
In another column place the formula =Month(A1) where A1 is the first
cell in the column that holds the dates. Drag that formula down with the
fill handle. Then count the number of 1's. 2's, etc. HTH Otto

 

[Dave Peterson]

If you're doing this on a monthly basis, I would think that the year would be
important, too.

=sumproduct(--(text(a1:a10,"yyyymm")="201001"))

This still expects your values to be real dates -- I'm hoping that you just
formatted the cells to display mm/yy.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Ms-Exl-Learner]

Assume that your Dates are in A1 to A100 and you would like to get the count
of dates for the month of January 2010.

=SUMPRODUCT((A1:A100>=DATE(2010,1,1))*(A1:A100<=DATE(2010,1,31)))

Change the cell Range A1: A100 to your desired Range, if required.

Remember to Click Yes, if this post helps!

 

[Ms-Exl-Learner]

Assume that your Dates are in A1 to A100 and you would like to get the count
of dates for the month of January 2010.

=SUMPRODUCT((A1:A100>=DATE(2010,1,1))*(A1:A100<=DATE(2010,1,31)))

Change the cell Range A1: A100 to your desired Range, if required.

Remember to Click Yes, if this post helps!