I am creating a construction punch list with 5 columns, two of the columns are DUE DATES and COMPLETED DATES. I am trying to get the Due Date cells to turn red once the date entered has passed and as long as there is no date entered into the Completed dated cell. I am also trying to have the completed date cell turn green once a completed date is entered and have the due date column to turn back white. Is this possible?

Hi AndrewEnos, If you can rephrase the first condition to give a definitive date period youre trying to change once passed. example - Say if Due date entered has passed today's date - then I should be able to help otherwise, what I read above is vague for me to work with.

SECOND CONDITION: The formula below should help you figure out the second condition (I am also trying to have the completed date cell turn green once a completed date is entered and have the due date column to turn back white.)
- What you should do is, Highlight all cells you want this to affect in COMPLETED DATES (SAY B2:B700), click on the Home Tab, Conditional Formatting, Click on New Rule..., Select from the rule type - Use a Formula to determine which cells to format and Enter in the formula box =B2:B700<>""
Next click on Format, click on Fill and choose your color Green, OK, OK and that should do it.

Then on the DUE DATE (Repeat as above and in the Formula to format insert the =B2<>"" assuming Colums B is the Completed Date Column and continue with the process and choose White. This looks at the column B and if no value it turns white.

Is this what you want? If so, check out the conditional formatting rules I set up. It will give you an idea of some of the ways you can set them up. And remember, rule precedence goes from top to bottom i.e. the first rule takes precedence over the second which takes precedence over the third, etc.

Attached Files

Test.xlsx (9.7 KB, 18 views)