0

I have a spreadsheet for taskers with due dates. I have used conditional formatting to change the font color to red for pass due and yellow for approaching due dates...now i need to add another condition to change the font in the same column back to black when i input the completion date in the next column. Due Date is in Column E and Completion Date is in Column F. Any help with the formula will be greatly appreciated.

1 Answer 1

0

You can combine conditions by using a Rule Type option of "Use a formula to determine which cells to format". So, say your original rule was to set the cell to be yellow if it's within 10 days of today. That would be:

=(E2 - TODAY() < 10)

To add a condition to ignore the rule if the completion date has been set (i.e. something in column F), use the AND function like this:

=AND((E2 - TODAY()  < 10),F2="")

To be complete you would want to include a check to ignore this rule for past days:

=AND((E2- TODAY() < 10),E2 - TODAY() >0,F2="")

The rule for past uncompleted tasks is easier:

=AND(TODAY() >= E2,F2="")

Unfortunately, Excel doesn't seem to allow relative references in conditional formulas, so you'll have to paste this in other cells one by one, instead of by a range. (There is probably an alternative way to apply this to a range?)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .