Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Conditional formatting for past dates dependent on other past dates

 

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?

 

 

PS_Smartsheet question.JPG

Comments

  • Travis
    Travis Employee

    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
    edited 05/04/16

    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
    Travis Employee
    edited 05/10/16

    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)

     

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

     

    =IF(Finish2 > Baseline2, 1)

     

    Hope this helps! 

This discussion has been closed.