Date without true value
 

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

Date without true value
 

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))

--
Biff
Microsoft Excel MVP


"Rubem" wrote in message
...
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

Date without true value
 

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

"Rubem" wrote in message
...
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

Date without true value
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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




Rubem wrote:

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

--

Dave Peterson

Date without true value
 

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<=DA TE(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)
--------------------


"Rubem" wrote:

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

Date without true value
 

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<=DA TE(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)
--------------------


"Rubem" wrote:

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