Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / February 2007

Tip: Looking for answers? Try searching our database.

Conditional Formatting - due date

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rod from Corrections - 05 Feb 2007 18:49 GMT
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 - 05 Feb 2007 19:23 GMT
Hi Rod

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

Signature

Regards

Roger Govier

>I need some help with Conditional Formatting, please.
>
[quoted text clipped - 19 lines]
> helpful to have a bunch of red cells where the report has already been
> submitted.
Rod from Corrections - 05 Feb 2007 19:56 GMT
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
>
[quoted text clipped - 24 lines]
> > helpful to have a bunch of red cells where the report has already been
> > submitted.
Roger Govier - 05 Feb 2007 20:17 GMT
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)

Signature

Regards

Roger Govier

> Thanks for the help, Roger, but I couldn't get it to work.  I am a
> novice at
[quoted text clipped - 43 lines]
>> > been
>> > submitted.
Rod from Corrections - 05 Feb 2007 21:39 GMT
Perfect!  Thanks very much, Roger.

> Hi Rod
>
[quoted text clipped - 51 lines]
> >> > been
> >> > submitted.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.