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

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


--
Regards
Roger Govier

"Ginger" wrote in message
...
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

Hi,

If you want to exclude both dates you need AND and not OR, Try this

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

If you have a long list of non contiguous dates to exclude then it may
be best to put them in a column abd do a vlookup.

Mike




On Sun, 24 May 2009 09:18:36 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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

Roger apologies I appear to have replied to you, this place is all
screwed up --- again ---.

Mike

On Sun, 24 May 2009 09:26:20 +0100, Mike H < wrote:


Hi,

If you want to exclude both dates you need AND and not OR, Try this

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

If you have a long list of non contiguous dates to exclude then it may
be best to put them in a column abd do a vlookup.

Mike




On Sun, 24 May 2009 09:18:36 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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

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
---------------
Jacob Skaria


"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

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

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

--
Biff
Microsoft Excel MVP


"Ginger" wrote in message
...
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

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?

"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

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

--
Biff
Microsoft Excel MVP


"Ginger" wrote in message
...
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?

"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

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(2 009,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(2 009,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




"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

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=DA TE(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.

--
Biff
Microsoft Excel MVP


"Ginger" wrote in message
...
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(2 009,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(2 009,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




"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

On Sun, 24 May 2009 09:18:36 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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.

On Sun, 24 May 2009 02:51:04 -0700, Jacob Skaria
wrote:

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
---------------
Jacob Skaria


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.

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.

Hi

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

--
Regards
Roger Govier

"TheQuickBrownFox" wrote in message
...
On Sun, 24 May 2009 09:18:36 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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.

On Fri, 29 May 2009 23:58:28 -0700, TheQuickBrownFox
wrote:

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

On Fri, 29 May 2009 23:58:28 -0700, TheQuickBrownFox
wrote:

Does placing them there by hand force it as well?

no
--ron

On Sat, 30 May 2009 06:58:09 -0400, Ron Rosenfeld
wrote:

On Fri, 29 May 2009 23:58:28 -0700, TheQuickBrownFox
wrote:

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

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.

On Sat, 30 May 2009 09:08:37 -0700, TheQuickBrownFox
wrote:

On Sat, 30 May 2009 06:58:09 -0400, Ron Rosenfeld
wrote:

On Fri, 29 May 2009 23:58:28 -0700, TheQuickBrownFox
wrote:

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

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