Count dates in colume that does not equal to certain year & month

[balwy]

Hi,

Does anyone know how to count dates in column that does not equal to certain year and month?

For eg: I want to calculate the number of dates that do not fall with Mar-2015 (excluding the blank fields).

I tried using the sumproduct function but it gives me error code "#VALUE!"

=SUMPRODUCT(--(TEXT(X5:X14,"yyyymmm")<>(--(TEXT(Z8,"yyyymmm")))))

Month : Mar-2015

End Date

12-May-2015

14-Apr-2015

27-Feb-2015

24-Mar-2015

4-Jun-2015

31-Mar-2015

<tbody>
</tbody>

Thanks.

<colgroup><col></colgroup><tbody>
</tbody>

 

[James006]

Hi,

The very first question you do have to ask yourself is the following :

In my database, am I using strings or actual numbers formatted as Dates ...???

 

[balwy]

Hi,

It is actual numbers formatted as dates.

 

[James006]

Hi,

With your reference month in cell C1 ... you could test ... =SUMPRODUCT((MONTH(A2:A12)<>MONTH(C1))*(A2:A12<>""))

 

[balwy]

Hi,

It works! Thanks so much

But I probably need some time to understand how the logic works...

 

[James006]

Hi,

Glad you solved your problem ...

Thanks for the feedback ...

 

[Aladin Akyurek]

Including the year test...

=SUMPRODUCT(1-(TEXT(X5:X14,"yyyymmm")=Z8))

where Z8 = 'May-2015.

Or if Z8 = 1-May-2015 (a true date)...

=SUMPRODUCT(--(X5:X15-DAY(X5:X15)+1=Z8))

Or again

with Z8 = 1-May-2015...

=COUNTIFS(X5:X14,">="&Z8,Z5:X14,"<="&EOMONTH(Z8,0))

 

[James006]

Hello Aladin,

With only 5 months in 2015 ...missed the constraint of the year ...

Thanks for your solutions ..