View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TheQuickBrownFox TheQuickBrownFox is offline
external usenet poster
 
Posts: 8
Default Dates and If Function

On Sun, 24 May 2009 08:16:56 -0400, Ron Rosenfeld
wrote:

On Sun, 24 May 2009 00:26:01 -0700, Ginger
wrote:

I need to check a cell to see that it does not contain a certain date. There
are 12 dates that the cell cannot be.

B12 = Date that I am testing to make sure that it isn't one of the dates in
my formula.

I have tried =IF(OR(B12<"4-5-2009", B12<"5-7-2009", "Y","N")

I can't get this to work. The cell format for B12 is "5-Apr-2009.
That differs from what appears in the top box when I click on the cell. It
shows as 4/5/2009. Could that make a difference? I have tried both ways,
and I can't get this to work. Any help would be greatly appreciated!

Thanks,

Ginger


Several problems with your formulation:

1. You are missing a parenthesis after your conditional_test.
2. You need to translate your date Textstrings in the formula to real dates;
and it would be better practice to use unambiguous date constructs.
3. Your conditional testing will always evaluate to TRUE. (Read HELP for how
the OR function works).

The following is one solution:

=IF(AND(B12<DATEVALUE("4-5-2009"), B12<DATEVALUE("5-7-2009")), "Y","N")

This is arguably better:

=IF(AND(B12<DATE(2009,4,5), B12<DATE(2009,5,7)), "Y","N")

But since you have 12 possible dates, I would list those dates in some
contiguous range and then use the array formula:

=IF(AND(B12<ExcludedDates),"Y","N")

This formula must be **array-entered**:

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
--ron



Does placing them there by hand force it as well?

It just would be interesting to know is all.