conditional format a weekend date

[mart10]

I am using Excel 2003

How can I contionally format for dates at a weekend to be in different colour?

 

[SkipVought]

hi,

Use the TEXT function to return the "ddd" as...
[tt]
=OR(TEXT(A1,"ddd")="Sat",TEXT(A1,"ddd")="Sun")
[/tt]
assuming that column A is your datecolumn.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

SORRY, CONFUSED

 

[BobStubbs]

You can also do this with the WEEKDAY function. This returns a digit between 1 (Monday) and 7 (Sunday) if Windows dates are set up in the standard way.

Try:

=OR(WEEKDAY(A1)=6,WEEKDAY(A1)=7)

If you want Saturday and Sunday to be different colours, set two conditional formats on the cell, just using:

WEEKDAY(A1)=6

for Saturday, and =7 for Sunday

Bob Stubbs (London, UK)

 

[mart10]

I just have a formula in the cell D20 saying =$D$5+1 I change D5 and want D20 to remain white background unless the calculated date is on a weekend in which case turn red Best way forward?

 

[SkipVought]

What is the VALUE that is displayed in D20? If it is your date, then change the CF formula accordingly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

Yes it is a date that is displayed but putting that formula in does nothing

 

[SkipVought]

WHERE are you putting the CF Fromula? Please explain in detail.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

I am putting it in Format, Contional Formatting, setting to a formula, pasting in formula you gave and then upon condition changing the colour

 

[SkipVought]

Please post the EXACT formula you have in your CF.

The REFERENCE in you CF formula must point to the cell in which the date of interest resides.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeterMoran]

Hi mart10,

Using BobStubbs details above proceed as follows:

Select the appropriate cell for Cond Formatting.

Select the Format menu, Conditional Formatting item.

Cond Format Window should now be present.

On the left select the pulldown beside "Cell Value is", and select "Formula is".

The window will now change and you can enter the first of BobStubbs formulas.

Then select the red formatting you require via the Format button.

Finally select OK, OK to exit the formatting and the CD window.

Check that it works as required.

You should now have the Cond Formatting you need.

Good Luck,

Peter Moran

 

[mart10]

SORRY IT WORKED, THANKS AGAIN

 

[SkipVought]

Why would you be sorry it worked?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}