Conditional Formatting Comparing Dates

kss5229

I want to have conditional formatting comparing 2 dates:

Where [Actual] > [Due Date], column will be red

Where [Actual] < = [Due Date], column will be green

Where [Due Date] has a value but [Actual] is blank, I would like to assume today's date for Actual.
Where [Due Date] is blank, but [Actual] is not, I'd like it to be green.
Where [Due Date] & [Actual] are blank, I would like it to not evaluate/not be colored red or green.

I know I need a helper column, but I'm getting stuck on how to implement it to evaluate properly where dates are left blank.

Paul Newcome

Give this a try:

=IF([Due Date]@row <> "", IF([Due Date]@row > IF(Actual@row <> "", Actual@row, TODAY()), "Green", "Red"), IF(Actual@row <> "", "Green"))

Michelle Choate 2

The first part is easy =IF([Actual]@row > [Due Date]@row, "Red", IF([Actual]@row < = [Due Date]@row, "Green"

As for the second part….

=IF(AND([Due Date]@row = "", Actual@row = ""), " ", IF(AND([Due Date]@row <> "", Actual@row = ""), IF(TODAY() > [Due Date]@row, "Red", IF(TODAY() < =[Due Date]@row, "Green")), IF(AND([Due Date]@row = "", Actual@row <> ""), "Green")))

Paul Newcome

Give this a try:

=IF([Due Date]@row <> "", IF([Due Date]@row > IF(Actual@row <> "", Actual@row, TODAY()), "Green", "Red"), IF(Actual@row <> "", "Green"))