Conditional formatting based on dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ann - 30 Nov 2007 15:57 GMT

I would like to shade cells in my spreadsheet based on information contained
within two date fields:
Column E = Target Date
Column F = Complete Date (left blank until task completed)
Column G = Status (shaded yellow if today's date is equal to col. E and col.
F is null; shaded red if today's date is past col. E and col. F is null;
shaed grey if col. F contains any date (is not null).

Any help would be appreciated.

Reply to this Message

Nigel - 30 Nov 2007 17:32 GMT

You need to set up conditional formatting for the cells.

Select the first cell in column G that you wish to control. Goto to the
menu item Format and choose Conditional Formatting.

In the left hand box condition 1, select 'Formula Is' , in the box next to
the right type in =AND($E4=TODAY(),$F4=0)
(Note: I have chosen row 4 above, change to suit your first row); then in
click the Format option and set the Pattern to Yellow.

Click Add>> and in condition 2 set 'Formula Is' and enter in box
=AND(($E4<TODAY(),$F4=0)
Set pattern to Red.

Click Add>> and in condition 3 set 'Formula Is' and enter in box =F8>0
Set pattern to Grey.

Press OK and copy the cell down to all rows to be monitored.

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

>I would like to shade cells in my spreadsheet based on information
>contained
[quoted text clipped - 7 lines]
>
> Any help would be appreciated.

Reply to this Message