Large of a Date Array only working with nested IF, not AND

I have a working formula, I would just like to know why it works the way I got it to work and not my original way.

Excel table containing job value, date the job was generated, and date the job was signed (only if it was signed).

I've been tracking data on it using:

Please Login or Register  to view this content.

This has been working fine since I started keeping track in June. However now that we're on a new year, when I do month = 1, I get jobs that have no date entered into the date signed column of my table. I got around this just fine by:

Please Login or Register  to view this content.

I'm very curious why month()=1 includes blank values, if you'd like to answer that, but that's not my main question.


This worked just fine in my sumproduct, but I also have an array I've been using to find the max values for a month:

Please Login or Register  to view this content.

Like the other formulas, this started including blank rows when I moved into January. So I tried using AND(Year()=2013, Month()=1:

Please Login or Register  to view this content.

However I found that this is no different than just doing MONTH()=1. It includes blank dates. But if I nested the if(month()) inside the year, it works:

Please Login or Register  to view this content.

Can someone explain why my nested if worked, but the AND didn't?

AND doesn't work with arrays - you could achieve it using this:

=LARGE(IF((MONTH(Table1[Date Signed])=1)*(YEAR(Table1[Date Signed])=2013),Table1[Amount],),1)

To answer your earlier question, a blank cell is often interpreted as having the value of 0 in excel formulae, and the date which has a zero serial number in Excel's date scheme is 0th January 1900.

It's always a good idea to specify the year that you are considering, as well as the month, but another way to distinguish cells that are blank is to have a term:

Table1[Date Signed]<>""

Hope this helps.

Pete