Conditional formatting for past dates dependent on other past dates

Kristina Dojillo

 

Hello,

Is there a way to condtionally format dates in the past based on other dates in the past?  For example, we are transitioning our project schedule from excel to smartsheet.  We have dates in the past as shown below.  In excel they are conditionally formatted to turn red if the Actual happened after the Current Target.  If the Actual happened on the same day or before the Current Target, the format stays black.

 

We haven't found an option in the SmartSheet conditional format menu that lets a cell's format depend on another cell's content.  We can only choose one "in the past" condition.  We can't choose "further in the past than Column 12".  Please advise?

 

 

Travis

Try adding a Checkbox column to your sheet that checks the box if the Actual date is after the Current Target date. Here is the formula you can use (just update the cell references):

 

=IF(Actual1 > [Current Target]1, 1)

 

Now, set your formatting rule based on the Checkbox column. 

 

 

Hope this helps!

Cliffinkent

Hey, 

 

I've used the technique above, but it's not working properly. Here's what I've got

 

=IF(Finish2 > Baseline2, 1, 1)

 

Where Finish2 is the first cell with my actual date, and Baseline 2 is the first cell with my target date

 

No matter what I try it ticks the box is actual is greater than OR Equal to target date. Not just great than. Which goes against what the forumla is saying.

 

Any help here would be great.

 

Thanks!

Travis

Hi Cliffinkent, sorry for the delayed response! 

 

The box is always checked because that is what your formula is telling it to do. Here's how an IF statement works:

 

 IF(logical_test, value_if_true, value_if_false)

 

Your formula:

 

 IF(Finish2 > Baseline2 [logical_test], value_if_true [1], value_if_false[1])

 

Your formula says if the statement (Finish2 > Baseline2) is TRUE or FALSE then 1, which checks the box.

 

Try this:

 

=IF(Finish2 > Baseline2, 1, 0)

 

A value_if_false is not always necessary. You could also set it up like this:

 

=IF(Finish2 > Baseline2, 1)

 

Hope this helps!