Conditional Formatting - due date

[Guest]

I need some help with Conditional Formatting, please.

I have a spreadsheet used to track data relating to reports prepared for
clients. Column F on each row contains the date the report is due. Column
G contains the actual date when the completed report is submitted.

I would like to add conditional formatting to Column F. I can manage the
first part, which would add yellow background to cells with dates within the
next 3 days and red background to cells where the report is due today or
overdue.

However, the part I am stumped on is: I would like to have no background
once any value is entered into column G. Once the report has been
submitted, there is no longer any need to have color alerts, and it is not
helpful to have a bunch of red cells where the report has already been
submitted.

 

[Roger Govier]

Hi Rod

Add an AND to your formatting formulae
=AND(G1="",TODAY()-F1>=0)

--
Regards

Roger Govier


"Rod from Corrections" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

Thanks for the help, Roger, but I couldn't get it to work. I am a novice at
Conditional Formatting and functions, so I probably got the syntax wrong.

Do you mean to add this string [=AND(G4="",TODAY()-F4>=0)] as one of the
Conditions (i.e. condition #3), or to add it to one of the existing
conditions?

I tried to use =(F4-TODAY())<=1 AND(G4="",TODAY()-F4>=0), but got a syntax
error.

 

[Roger Govier]

Hi Rod

Your 1st Condition for Yellow format needs to be Formula IS
=AND(G4="",F4-TODAY()<4)

Your 2nd condition for the Red Format needs to be Formula Is
=AND(G4="",TODAY()-F4>=0)

--
Regards

Roger Govier


"Rod from Corrections" <[email protected]>
wrote in message

Thanks for the help, Roger, but I couldn't get it to work. I am a
novice at
Conditional Formatting and functions, so I probably got the syntax
wrong.

Do you mean to add this string [=AND(G4="",TODAY()-F4>=0)] as one of
the
Conditions (i.e. condition #3), or to add it to one of the existing
conditions?

I tried to use =(F4-TODAY())<=1 AND(G4="",TODAY()-F4>=0), but got a
syntax
error.

Hi Rod

Add an AND to your formatting formulae
=AND(G1="",TODAY()-F1>=0)

--
Regards

Roger Govier


"Rod from Corrections" <[email protected]>
wrote in message

 

[Guest]

Perfect! Thanks very much, Roger.

Hi Rod

Your 1st Condition for Yellow format needs to be Formula IS
=AND(G4="",F4-TODAY()<4)

Your 2nd condition for the Red Format needs to be Formula Is
=AND(G4="",TODAY()-F4>=0)

--
Regards

Roger Govier


"Rod from Corrections" <[email protected]>
wrote in message

Thanks for the help, Roger, but I couldn't get it to work. I am a
novice at
Conditional Formatting and functions, so I probably got the syntax
wrong.

Do you mean to add this string [=AND(G4="",TODAY()-F4>=0)] as one of
the
Conditions (i.e. condition #3), or to add it to one of the existing
conditions?

I tried to use =(F4-TODAY())<=1 AND(G4="",TODAY()-F4>=0), but got a
syntax
error.

Hi Rod

Add an AND to your formatting formulae
=AND(G1="",TODAY()-F1>=0)

--
Regards

Roger Govier


"Rod from Corrections" <[email protected]>
wrote in message
I need some help with Conditional Formatting, please.

I have a spreadsheet used to track data relating to reports
prepared
for
clients. Column F on each row contains the date the report is due.
Column
G contains the actual date when the completed report is submitted.

I would like to add conditional formatting to Column F. I can
manage
the
first part, which would add yellow background to cells with dates
within the
next 3 days and red background to cells where the report is due
today
or
overdue.

However, the part I am stumped on is: I would like to have no
background
once any value is entered into column G. Once the report has been
submitted, there is no longer any need to have color alerts, and it
is
not
helpful to have a bunch of red cells where the report has already
been
submitted.