Dates and If Function

[Ginger]

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

 

[Jacob Skaria]

You have mentioned you have 12 dates that the cell cannot be..Better to
create a named range with the 12 dates and MATCH() value in B12 with that to
see whether there is a match..If there is a match return "Invalid Date"

OR

If you dont want to have named range try the below... You can add up the
other dates into the array
{"24052009","23052009","date3","date4","date5",....} You can mention the
format as ddmmyyyy or mmddyyyy and place the dates accordingly..

=IF(ISERROR(MATCH(TEXT(B12,"ddmmyyyy"),{"24052009","23052009"},0)),"","Invalid Date")

If this post helps click Yes

 

[Ron Rosenfeld]

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

 

[T. Valko]

For 12 dates, list these dates in a range of cells, say, A1:A12.

Then use this formula:

=IF(COUNTIF(A1:A12,B12),"N","Y")

 

[Ginger]

Thanks, guys! I got it to work on one worksheet, but when I reference that
cell in another worksheet, it doesn't work. Any ideas about why that would
be?

 

[T. Valko]

Explain what "it doesn't work" means and post the formula you tried that
"doesn't work".

 

[Ginger]

Okay, sorry . . .

The formula that works on one file is this and, yes, I guess i did it the
hard way, but it worked!

=IF(OR($B$12=DATE(2009,4,28),$B$12=DATE(2009,4,29),$B$12=
DATE(2009,6,23),$B$12=DATE(2009,6,24),$B$12=DATE(2009,8,11),
$B$12=DATE(2009,8,12),$B$12=DATE(2009,9,22),$B$12=
DATE(2009,9,23),$B$12=DATE(2009,11,3),$B$12=DATE(2009,11,4),
$B$12=DATE(2009,12,15),$B$12=DATE(2009,12,15)),"Y","N")

But when I try to put this formula on another worksheet referencing the same
B12 cell, it won't work.

Any ideas? Thanks.

Ginger

 

[T. Valko]

If B12 is on a different sheet then you need to include the sheet name...

=IF(OR(Sheet1!$B$12=.........

That's going to make your formula *really* long!

I strongly suggest you enter the dates in a range of cells then use a much
easier formula like:

=IF(COUNTIF(A1:A10,Sheet1!B12),"Y","N")

But, if you insist on not listing the dates in a range of cells you can
reduce your current formula to this since the dates you're checking are in
sequences of 2 consecutive dates. Include the sheet name when referencing
B12 on a different sheet.

=IF(OR($B$12=DATE(2009,4,28)+{0,1},$B$12=DATE(2009,6,23)+{0,1},,$B$12=DATE(2009,8,11)+{0,1},$B$12=DATE(2009,9,22)+{0,1},$B$12=DATE(2009,11,3)+{0,1},$B$12=DATE(2009,12,15)+{0,1}),"Y","N")

Note that in the formula you posted you repeated the date 12/15:

$B$12=DATE(2009,12,15),$B$12=DATE(2009,12,15)

I'm assuming the pattern continues and you really meant 12/15 and 12/16.

 

[TheQuickBrownFox]

Hi Ginger

If you are using 05 Apr 2009 in the cell, then the comparison should be
05-04-2009.
However, when you have the date within quotes, it is Text, whereas the dates
in your columns, although appearing like text, are stored internally as
serial numbers.
You need to coerce the Text values in your formula to numbers, by placing
the double unary minus in front of them

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

An alternative, which makes the Date quite explicit is to use the date
function Date(Year, Month, Day)
=IF(OR(B12<>DATE(2009,4,5), B12<>DATE(2009,5,7), "Y","N")

Is there a code segment that is good for testing for leap years so that
a February calendar can be properly sized/adjusted? You seem extremely
date function knowledgeable.

 

[TheQuickBrownFox]

You have mentioned you have 12 dates that the cell cannot be..Better to
create a named range with the 12 dates and MATCH() value in B12 with that to
see whether there is a match..If there is a match return "Invalid Date"

OR

If you dont want to have named range try the below... You can add up the
other dates into the array
{"24052009","23052009","date3","date4","date5",....} You can mention the
format as ddmmyyyy or mmddyyyy and place the dates accordingly..

=IF(ISERROR(MATCH(TEXT(B12,"ddmmyyyy"),{"24052009","23052009"},0)),"","Invalid Date")

If this post helps click Yes

OK... That last bit there looks very much like what I would need to
test for leap year by way of examining the formatted result of 2/29/yyyy.

Could you help me here?

If the result is 3/01/yyyy or remains as 2/29/yyyy is the test
comparison after setting a date of "2/29/yyyy". I could set a 'flag' as
the text in a given cell and refer to it later for other code.

My final goal really is to adjust a "February" worksheet and a February
chart worksheet, which is on a separate sheet.The chart shows an error if
the sheet is 28 days, but the chart is formatted to a 29 row data set,
whether there is data on the sheet range or not.

I want to dynamically adjust the chart data set. I have tried named
ranges and all kinds of other methods to make the chart, but it doesn't
like named ranges. I may be able to adjust one axis on the fly, however.
Not sure though. Do you see an easy test function not far from what you
just authored here?

There is also a hard test for leap year relating to being divisible by
4, 100, and 400, which may be easier still to code. I just do not
possess the logic for it apparently.

 

[TheQuickBrownFox]

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.

 

[Roger Govier]

Hi

One way
=ISNUMBER(--"29/02/08") will return True
=ISNUMBER(--"29/02/09") will return False

 

[Ron Rosenfeld]

Does placing them there by hand force it as well?

It just would be interesting to know is all.

That would seem to be a question more easily and quickly answered by your
testing it.
--ron

 

[Ron Rosenfeld]

Does placing them there by hand force it as well?

no
--ron

 

[TheQuickBrownFox]

That would seem to be a question more easily and quickly answered by your
testing it.
--ron

Or by a simple reply by someone that has intimacy with it. Duh. This
is a discussion group, not a go try it and see declaration by nose
thumbing twits group.

 

[Ron Rosenfeld]

Or by a simple reply by someone that has intimacy with it. Duh. This
is a discussion group, not a go try it and see declaration by nose
thumbing twits group.

Yes, and had you read the discussion prior to firing off your response, you
wouldn't even have had to try out your supposition.

But I suppose different people learn differently. Some learn by doing; others
can't be bothered.
--ron