Conditional formatting date

[scott]

Xl 2000 user.

We have a vacation template with the day of the month listed as
(1,2,3,etc) starting in A6. The months are column headings starting in
row 4 and are date formats listed as 12/1/03,1/1/04, etc.

I set up conditional formats to change the patterns for weekend days.
Those are :
=WEEKDAY(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=1
=WEEKDAY(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=7

I tried to set up a third condition that will gray the incorrect days
(i.e. November 31st)
=MONTH(DATE(YEAR(AK$4),MONTH(AK$4),$A36))>MONTH(AK$4)
This works for November, but does not work for any month with 31 days.
If I look at both sides of the formula, they equal
In Ak44 =MONTH(DATE(YEAR(AK$4),MONTH(AK$4),DAY($A36)))=12
In AK45 =MONTH(AK4) =12
And in AK46 =IF(AK44>AK45,"Invalid Date","OK") = "OK"

 

[Bob Phillips]

Scott,

Use <>

=MONTH(DATE(YEAR(AK$4),MONTH(AK$4),$A36))<>MONTH(AK$4)

and grey where the condition is True, or use =

=MONTH(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=MONTH(AK$4)

and grey where the condition is False

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[scotto_wi]

Bob,

Thanks for your reply. I originally started the formula <>, but trie
to change it to > based on looking at December. Did you test th
formula, because no matter what I tried I could not get it to work fo
the 31st of the month. I must be missing something because it seem
pretty straightforward.


Scot

 

[scott]

I fugured it out. The "default" format was already grayed out. I knew
I missed something stupid.

Thanks for your help.