Stuck on conditional formatting

Molly12345

Hi, I have a due date, an allowed tolerance window of dates (either +/- a month, or +/- 3 months, depending on the project) and a completed date.

I want to set up conditional formatting for the completed date cell ,to turn to a green background if it was completed by the due date.

To turn yellow if completed within the allowed tolerance, and red if completed outside the tolerance. Is this possible ? Many thanks in advance

Paul Newcome

Try something like this:

=IF([Completed Date]@row <> "", IF([Completed Date]@row < [Next Due Date]@row - 30, "Early", IF([Completed Date]@row <= [Next Due Date]@row, "Yes", "No")))

Paul Newcome

You would need a helper column (that can be hidden after setup) with a formula that is going to output something based on the comparison. You would then base your conditional formatting off of this helper column.

I am also thinking that you are probably going to want two other (hidden) helper columns that will output the start and end dates on each row for the window of tolerance to make your formula in the first helper column more manageable.

But first things first (or third in this case haha)... We need to clear something up with your ask.

You say there is a tolerance window that is +/- a certain number of months. You then say "Green" if completed before due date, "Yellow" is in tolerance window, and "Red" if outside of tolerance window.

This muddies the waters a bit with Completed Dates that come before the Due Date.

If the window is +/-1 month and the Completed is 2 weeks before the Due, is that Green since it was completed before or Yellow since it is within the window? What if it was completed 6 weeks early? Green because it is early or Red because it is outside the window?

Molly12345

Hi Paul,

Thanks so much ! Great Idea, I used my "completed by due date", and "completed by end of tolerance column" column as helpers.

After sorting this problem, I have ran into another. I am extremely bad at formulas. I now need to put in a formula for the "completed by due date" column to say:

1.     If "completed by date" column is blank leave "completed by due date" blank

2.     If the "completed by date" is completed 31 days+ early of the " due date" = early ( just an idea, can leave out if too complicated)

3.     if the "completed by date" is less than or = to "completed by due date" = "Yes" ( I do not know if this can be done with the above?

4.     If the "completed by date" is greater than the "due date"= "No"

I was not getting anywhere with the formula and was manually entering !

Thank you very much in advance :)

Paul Newcome

Try something like this:

=IF([Completed Date]@row <> "", IF([Completed Date]@row < [Next Due Date]@row - 30, "Early", IF([Completed Date]@row <= [Next Due Date]@row, "Yes", "No")))

Molly12345

Hi Paul,

Just not to waste your time. I figured out my formula issue. I did not have one of the columns set as a date 🙄. Thanks again for the timely response above !

Molly12345

Sorry Paul I did not see your helpful function above ! thank you so much! That was super helpful!