SUMIFS displaying date as a number

[corte]

I'm trying to create a SUMIFS statement for a date range.

My formula is: =SUMIFS(N:N,M:M,">="&Q5,M:M,"<="&Q6)

Sum_range (N:N)
Criteria_range1 (M:M)
Criteria1 ">="&Q5
Criteria_range2 (M:M)
Criteria2 ">="&Q6

Q5 = 10/1/2010
Q6 = 09/31/2011

The problem is on Criteria1. It returns ">=40452" as a result. However, Criteria2 correctly returns "<=09/31/2011".

Any ideas on what I am doing wrong?

Thanks!

 

[stilesbn]

Is the cell returning 40452 formatted as a date?

 

[corte]

Yes. Cells Q5 and Q6 are formatted as a Date in the '3/14/2001' format.

 

[Aladin Akyurek]

Yes. Cells Q5 and Q6 are formatted as a Date in the '3/14/2001' format.

09/31/2011 in Q6 is not a date. Try:

=ISNUMBER(Q6)

for September counts 30 days.

Q5 on the other hand is a number (a date).

Hence the difference you observe.

 

[corte]

Ah! That's it. I changed the date to 9/30/2011 and it works. Thanks, Aladin!