Conditional formatting of dates in two cells using Excel 2007

L

lemur99

Hi,

I've got a spreadsheet set up as:
Column A - task
Column B - Expected completion date
Column C - Actual completion date

and I want to set it up so that all tasks that have not yet been completed
but should have been are highlighted in red (that is, for example, if B2<
todays date and C2 is blank then A2 is highlighted red).

I've been messing about but can't seem to get it right in the conditional
formatting - any suggestions?

thanks
 
O

OssieMac

Select cell A2
Select conditional format -> formula
Enter the following forumla
=AND(B2<TODAY(),C2="")
Set the required format then OK/apply etc until exit conditional format
Copy Cell A2
Paste Special -> Formats to the remaining cells to have the conditional
format.

In lieu of copy -> paste special you can also select the entire range to
format before opening conditional format just so long as you ensure that A2
is the active cell (The first cell in the selection or one that stays white
in the selection). You then enter the formula as if only applying it to only
one cell A2 exactly as above.
 
J

Jacob Skaria

1. Select the Range (say A1:A10) or column A. Please note that the cell
reference A1 mentioned in the formula is the active cell in the selection.
Active cell will have a white background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(B1<TODAY(),C1="",A1<>"",B1>0)

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK



If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top