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]

Okay then

 

[Jon von der Heyden]

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 ?)

I think that there are many lessons in these answers. The answers clarify the point Richard made about array constants needing to contain constant values only. There's one point made by me on how to create an array of date values using the date function, and another is a lesson on coercing values in an array constant. All good things that Jim could potentially use in other projects.

Personally I would go along with your suggestion and store the dates in a range, but I enjoy contributing other methods because of the other lessons that can be learned.

 

[jim may]

Thanks to all -- You guys really demonstate some depth in the Excel product, Wow!!

 

[wigi]

I think that there are many lessons in these answers. The answers clarify the point Richard made about array constants needing to contain constant values only. There's one point made by me on how to create an array of date values using the date function, and another is a lesson on coercing values in an array constant. All good things that Jim could potentially use in other projects.

Personally I would go along with your suggestion and store the dates in a range, but I enjoy contributing other methods because of the other lessons that can be learned.

Sure.

 

[jasonb75]

Jim, how do the 3 dates in the formula compare to current date?

I'm assuming (maybe incorrectly) that at some point in the future the month and year will change, so maybe you could EOMONTH and TODAY() to make it dynamic.

 

[jim may]

I ended up using:

=IF(AND(F11="Current Period Change",OR(C11=$H$6,C11=$H$7,C11=$H$8)),"OK","NOT")

This is included in a Quartery Inventory Template I'm designing for a client.

Thanks,

Jim