Invalid dates

[pilot]

I created a grid with months down left side and days (1-31) across top. Cells translate to date based on Row/Col intersection. This creates cells for invalid dates such as APR 31. Excel date functions treat this as MAY 1. Is there a way to make Excel see it as an invalid date instead? Ultimately want to use Conditional Formatting to black out such cells.

 

[Aladin Akyurek]

On 2002-02-24 12:47, pilot wrote:
I created a grid with months down left side and days (1-31) across top. Cells translate to date based on Row/Col intersection. This creates cells for invalid dates such as APR 31. Excel date functions treat this as MAY 1. Is there a way to make Excel see it as an invalid date instead? Ultimately want to use Conditional Formatting to black out such cells.

You want apparently to create a lookup table that tells you whether a given date is OK or Invalid.

I'll assume that A1:AF13 will house such a table with A2:A13 housing 3-letter month names and B1:AF1 the day numbers 1 to 31.

In A1 enter: 2002 [ the year of interest ]

In B2 enter:

=IF(TEXT(DATE($A$1,MATCH($A2,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"},0),B$1),"mmm")=$A2,DATE($A$1,MATCH($A2,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"},0),B$1),"Invalid")

Copy B2 first down till row 13 then across till column AF.

 

[pilot]

OK, I see what you did to compare the dates obtained in each method to see if the same or not (invalid). I think I can use this in my conditional format formula. But cells B2:AF13 are either blank or have other data in them, never the date. It must be inferred from the intersecting rows and column. I think I can get there from here, thanks Aladin.

 

[pilot]

Aladin,

I can't get there from here. It appears Excel will not allow array constants in Conditional Formatting statements. Now that you have a good handle on what I'm trying to do, do you have other ideas on how to get there?

 

[IML]

On 2002-02-24 14:04, pilot wrote:
Aladin,

I can't get there from here. It appears Excel will not allow array constants in Conditional Formatting statements. Now that you have a good handle on what I'm trying to do, do you have other ideas on how to get there?

For conditional formatting (and using aladin's assumptions), how about formula equal to:
=ISNUMBER(DATEVALUE(($A2&" "&B$1&", "&$A$1)))

in B2 and use format painter to hit the rest of the year?

good luck

 

[Guest]

On 2002-02-24 14:04, pilot wrote:
Aladin,

I can't get there from here. It appears Excel will not allow array constants in Conditional Formatting statements. Now that you have a good handle on what I'm trying to do, do you have other ideas on how to get there?

Gee, you succeeded to drag me into this cond format problem of yours. But, I admit, it's an interesting problem.

I added that (in)valid dates table to your workbook. The formulas that follow use that table to color your worksheet (implemented it for just one worksheet; I leave the rest to you).

Note. I didn't look into IML's proposal.

The formula for Condition 1:

=OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0))="Invalid"

The formula for Condition 2:

=WEEKDAY(OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0)),2)>5

where Base is the name of A1, MONTHS the name of the range A2:A13, and DAYS the name of the range B1:AF1 of the (in)valid dates table.

I think you'll need a 3rd condition for holidays (I didn't do that, which I also leave to you).

Aladin

PS. Your WB is underway to you.

 

[Aladin Akyurek]

Darn it: I didn't mean to post as Anonymous.

Aladin

=====================

On 2002-02-24 17:43, Anonymous wrote:

On 2002-02-24 14:04, pilot wrote:
Aladin,

I can't get there from here. It appears Excel will not allow array constants in Conditional Formatting statements. Now that you have a good handle on what I'm trying to do, do you have other ideas on how to get there?

Gee, you succeeded to drag me into this cond format problem of yours. But, I admit, it's an interesting problem.

I added that (in)valid dates table to your workbook. The formulas that follow use that table to color your worksheet (implemented it for just one worksheet; I leave the rest to you).

Note. I didn't look into IML's proposal.

The formula for Condition 1:

=OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0))="Invalid"

The formula for Condition 2:

=WEEKDAY(OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0)),2)>5

where Base is the name of A1, MONTHS the name of the range A2:A13, and DAYS the name of the range B1:AF1 of the (in)valid dates table.

I think you'll need a 3rd condition for holidays (I didn't do that, which I also leave to you).

Aladin

PS. Your WB is underway to you.

 

[pilot]

I knew you were good! Give me a few years and I may be dangerous with this stuff. Thanks very much for all your assistance (and everyone else here who keeps this thing rollin'.