Format cells

[wmbb]

I want to create a table in excel 2007 with on top the days of a year (see below).
In this table under saturday and sunday I want to fill the cells red.

X Friday, 01 Jan Saturday, 02 Jan Sunday, 03 Jan Monday, 04 Jan ..........
08.00 - 09.00
09.00 - 10.00
10.00 - 11.00
11.00 - 12.00
12.00 - 13.00
13.00 - 14.00
14.00 - 15.00
15.00 - 16.00

Is this possible with conditional formatting or Do I need VB-scripting ?

 

[lionelhill]

You could use a conditional format formula such as:
=OR(MOD(COLUMN(A1), 7)=6, MOD(COLUMN(A1), 7)=5)
(I put this one in A1 and then copied right and down as far as desired)

It doesn't look at the actual days, but since there are Saturdays and Sundays every seven days, regardless of anything else going on in the calendar, it should give the right effect.

This was an Excel2003 approach; there may be something cleverer in 2007.

 

[SkipVought]

Hi,

Yes it can easily be done with conditional formatting, texting the date in row 1 to be "Sat" or "Sun" using the TEXT() function.

Skip,

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

 

[wmbb]

I was thinking about using the function weekday()?
What should be the formula for the conditional format because the column is different for all cells ?

 

[wmbb]

="weekday(b1)=1 or weekday(b1)=7" does not work !!

 

[Loomah]

>> ="weekday(b1)=1 or weekday(b1)=7"
try
=or(weekday(b1)=1, weekday(b1)=7)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[wmbb]

Oke, I've got it working...

I've put the conditional format in cell b2 and copied this to the rest of the sheet.
The problem was the quotes excel placed automatically in the formula.
I removed the quotes and it works.

The formula I use now is: =OR(WEEKDAY(B$1)=1,WEEKDAY(B$1)=7)

Thanks for your support.

 

[SkipVought]

Mine was...
[tt]
=OR(TEXT(B$1,"ddd")="Sat",TEXT(B$1,"ddd")="Sun")
[/tt]


Skip,

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