Overdue items - condition formatting cell colour changes

[Audiophile]

Version: 2008
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Bob:

I apologize, but I'm not quite understanding how to do conditional formatting for an "overdue" item. I have a column titled "Due date". I'll use an example:

I have a due date of completing a task in a project for April 28th. If the date goes beyond, to April 29th I would like the cell to fill in with a red background. What formula would I use?

Thank you for your assistance and for your time.

 

[CyberTaz]

Assume the due date is in cell D5;

Select cell D5 then go to Format> Conditional Formatting. Open the left list
& select Formula Is then in the adjacent field enter the formula:

=D5>today()

Click the Format button & select the formatting of your choice.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Audiophile]

Perfect Formula! Thank you.

One more question regarding this.

If I put the word "Complete" in the cell when the task has been finished, is there a way that the Conditional Formatting can be automatically removed? When I do it now, the cell is still filled in red.

Thank you so much for your help.

 

[CyberTaz]

One option: Go back into the Conditional formatting dialog for the cell &
click the Add button. Cut the previous formula from the first field (you'll
need to use Command+X) then Paste it in as Condition 2.

Use the following formula as Condition 1;

=D5="complete"

But for Condition 1 don't specify any format change

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Audiophile]

Bob:

This does not seem to be working.

I have condition #1 as =D5="complete" (formatted as a blank white cell)
I have condition #2 as =D5>TODAY() (formatted as a red filled cell)

A) I get the correct format when the word "complete" is put into a cell (The word "complete" with a white background).

B) When I enter in yesterday's date into cell D5 I get a white background (when I would like to see a red back ground (as the task is now one day overdue)).

C) If I enter tomorrows date (the cell turns red, when it should be white, as the due date has not been reached yet)

I'm not sure what I've done wrong here. Could you please give me a hand? Thanks.

 

[CyberTaz]

Based on what you describe I'd say its just a matter of reversing your
comparison operator in Condition #2 - it should be < or <= rather than >.

IE; yesterday is less than today, tomorrow is greater than today.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Audiophile]

Bob:

This works, but I am left with one last question. I would like to have the cell remain white when there is no text in it at all. Using the two formulas in conjunction gives me a red cell when it is free of text. Should I be using a third condition to accomplish this? If so, what would that condition be? Thanks.

Now:

Condition #1 as =D5="complete" (formatted as a blank white cell)
Condition #2 as =D5<TODAY() (formatted as a red filled cell)

 

[CyberTaz]

There may be more elegant ways, but as long as you don't put a number in the
cell either of these as the only condition should give you what you want:

=AND(D5>0,D5<TODAY())

Or

=AND(D5<>"",D5<TODAY())

Regards |:>)
Bob Jones
[MVP] Office:Mac