Help with formula

[jim may]

My Cell C11 is a Date (numeric) - I need to test it for any one of 3 different month ends (below)... It currently want take this.. Any pointers? Tks Jim

=IF(AND(F11="Current Period Change",OR C11={Date(2011,1,31),Date(2011,2,28),Date(2011,3,31)}),"OK","NOT")

 

[wigi]

I would store the 3 dates in 3 different cells, possibily hidden from sight.
Then a simple lookup type formula will give you the result.
You will have a simpler formula and additional bonus is maintainability/extendability of your sheet.

Wigi

 

[Richard Schollar]

Array constants (bit between {}) can't take expressions (they are constants) so you need to use constants, or not use array constants eg:

=IF(AND(F11="Current Period Change",OR(C11=Date(2011,1,31),C11=Date(2011,2,28),C11=Date(2011,3,31)),"OK","NOT")

 

[Jon von der Heyden]

or:

=IF(AND(F12="Current Period Change",OR(C12=CHOOSE({1;2;3},DATE(2011,1,31),DATE(2011,2,28),DATE(2011,3,31)))),"OK","NOT")

Confirmed with Control+Shift+Enter

 

[Jon von der Heyden]

Or rather if the issue is serial dates:

=IF(AND(F12="Current Period Change",OR(TEXT(C12,"d-m-y")={"31-1-11","28-2-11","31-3-11"})),"OK","NOT")

 

[jim may]

Thanks Richard -- can you show an example of using the constants with the array?

 

[Richard Schollar]

For example:

=IF(AND(F12="Current Period Change",OR(C11={40574,40602,40633})),"OK","NOT")

Where 40574 = 31st Jan 2011 in serial terms, 40602 = 28th Feb 2011 etc

 

[Jon von der Heyden]

Another alternative might be to store the dates as constants and as text strings within the array constant, but bolt a "+0" to the end of the array constant to coerce them into their numerical values.

=IF(AND(F12="Current Period Change",OR(C12={"31-1-11","28-2-11","31-3-11"}+0)),"OK","NOT")

 

[wigi]

Hello guys,

it's interesting to see different approaches and functions, but do you really recommend usage like that ? (e.g. 40574,40602,40633 will not be recognizable to many people, or storing dates as texts ?)

 

[Richard Schollar]

I wouldn't recommend it but that wasn't the question