Thanks Biff. I never noticed that before. I just always assumed that they
started counting from 0 and not from 1. Computers like to do that... oddly
enough I was corrent although I was entirely wrong.
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

So day 0 is Jan 1, 1900.

This is a very difficult thing to explain!

Day 0 is actually Dec 31 1899 but as we know Excel doesn't recognize dates
before Jan 1 1900. However, you can calculate a "day 0" but you can't enter
the *date* Jan 0 1900.

According to Excel, Jan 1 1900 is a Sunday so logic tells us that Dec 31
1899 (also known as Jan 0 1900) must be a Saturday. That's why you get Sat
when you reference an empty cell with this formula:

=TEXT(A1,"ddd")

You can't enter the *date* 1/0/1900 in a cell but you can use a formula to
do so:

=DATE(1900,1,0)

So, "day 0" refers to the last day of the previous month *except* when the
MONTH function refers to numeric 0 or an empty cell.

=DATE(2010,1,0) = 12/31/2009
=MONTH(DATE(2010,1,0)) = 12

=MONTH(0) = 1
=MONTH(empty_cell) = 1
=MONTH(DATE(1900,1,0)) = 1

0 formatted as date = 1/0/1900

Doesn't make a bit of sense, does it?

--
Biff
Microsoft Excel MVP


"Jim Thomlinson" wrote in message
...
A blank cell is the same as a cell with 0 in it. Dates are stored as the
number of days that have elapsed since jan 1, 1900. So day 0 is Jan 1,
1900.
That is why your count is off for January. Add in a criteria to exclude 0.

=SUMPRODUCT(--(MONTH(A$3:A$87)=1), --(A$3:A$87< ""))

--
HTH...

Jim Thomlinson


"ronnomad" wrote:

I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
number of transactions for the time period. When I change the Month
number
to 2 the formula calculates correctly. However, for Month = 1, the
formula
is counting all the blank cells in the column as 1 also (I saw this using
the
formula auditing feature). A fix would be appreciated but I would also
like
to know why?

On a related topic, is there a way to use the Month feature to determing
Max
& Min values for the specified time period?


.