Conditional Formatting - using dates

[Geesan]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am working on a template for an Activity Report. In that report, you can set a start date and then you get a list of all the days in the month. The dates are split in two columns: one for the weekday (Monday, Tuesday,...) and one with the date itself (01/04/2009). This is done by formatting and formula. It looks like this

Startdate: 01-apr

Column1 Column2
=B5 =IF(B1="";"";B1)
=B6 =IF(B5="";"";B5+1)

--- Column1 just takes the date from the second column and formats it as dddd

Now I want to highlight the rows that represent the days of the weekend. I tried by saying that if field A4 (weekday) equals Saturday formatting needs to be done but that doesn't seem to work. Any idea if this can be done?

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am working on a template for an Activity Report. In that report, you can set
a start date and then you get a list of all the days in the month. The dates
are split in two columns: one for the weekday (Monday, Tuesday,...) and one
with the date itself (01/04/2009). This is done by formatting and formula. It
looks like this

Startdate: 01-apr

Column1 Column2
=B5 =IF(B1="";"";B1)
=B6 =IF(B5="";"";B5+1)

--- Column1 just takes the date from the second column and formats it as dddd

Now I want to highlight the rows that represent the days of the weekend. I
tried by saying that if field A4 (weekday) equals Saturday formatting needs to
be done but that doesn't seem to work. Any idea if this can be done?

What formula did you use in the conditional format? This should work fine
using weekday which will return 1 for Sunday, and 7 for Saturday.

 

[Geesan]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am working on a template for an Activity Report. In that report, you can set
a start date and then you get a list of all the days in the month. The dates
are split in two columns: one for the weekday (Monday, Tuesday,...) and one
with the date itself (01/04/2009). This is done by formatting and formula. It
looks like this

Startdate: 01-apr

Column1 Column2
=B5 =IF(B1="";"";B1)
=B6 =IF(B5="";"";B5+1)

--- Column1 just takes the date from the second column and formats it as dddd

Now I want to highlight the rows that represent the days of the weekend. I
tried by saying that if field A4 (weekday) equals Saturday formatting needs to
be done but that doesn't seem to work. Any idea if this can be done?

What formula did you use in the conditional format? This should work fine
using weekday which will return 1 for Sunday, and 7 for Saturday.
[/QUOTE]

I just used the standard 'Cell value is' - 'equal to' "Sunday". Perhaps this doesn't work since it refers to B5 and only takes part of the full date (only Sunday and not Sunday 05/04/2009)?

 

[Bob Greenblatt]

I just used the standard 'Cell value is' - 'equal to' "Sunday". Perhaps this
doesn't work since it refers to B5 and only takes part of the full date (only
Sunday and not Sunday 05/04/2009)?

Use a formula instead of cell value is. In the formula use weekday and check
for the value 1 or 7.

 

[Geesan]

Works like a charm! Thank you Bob