Conditional Formatting with IF for 2 values

[Dee2015]

I need column D, to change to "Overdue" when the date in Column H>today(), but only when column 'I' is blank, and if possible highlight in red the cell in column 'H' that is overdue. If status in column 'D' says "Complete", I dont want it to change anything.

I tried conditional formating on column 'H' for anything that was past todays date, but then it highlights in red also those which are completed and have a date in column 'I'

Any thoughts?

Thanks!

conditional .jpg

[FDibbins]

Try this in D...

=if(and(H3>TODAY(),I3="","Overdue","Pending")
Then you can use CF based on that column

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Dee2015]

Hi, Thanks for the formula, i was hoping to utilize conditional formatting since the report gets updated weekly by another database and the formulas would vanish, but I was able to utilize a Macro and set to input the formula you provided. Thanks for your help!

[FDibbins]

Conditional formatting only changes a cell's appearance, not its contents. And if you copy/paste over a cell that contains CF, that CF will be replaced