Formatting Cells with Color

Options
Lauren_C
Lauren_C
edited 12/09/19 in Smartsheet Basics

Hey All, 

Question for ya! Hoping someone can solve this for me. I have created conditional formatting to turn a cell GREEN if a date in a cell falls ahead of a projected date,  RED if a date in a cell falls after a projected date, and BLUE if a date lands exactly the same date as the projected date. 

Here is the formula i used and its working!

=IF([Floor Plan Approved]4 < [Floor Plan Approved]3, "True", IF([Floor Plan Approved]4 > [Floor Plan Approved]3, "False", IF([Floor Plan Approved]4 = [Floor Plan Approved]3, "Blue")))

However, the cells that are blank are turning the color if there is no date input in the cell. Its saying a blank cell is LESS THAN a date and showing green by default same for blue if projected cell is blank and the other cell is blank its auto BLUE. 

See Screenshot below. The grey rows are PROJECTION dates and the white rows are ACTUAL dates. 

If an ACTUAL cell has no date but the PROJECTION cell has a date the ACTUAL cell is defaulting GREEN since a blank is less than a date. If that makes sense.

 

MY GOAL: Is there any way around this and to get the colors to only show if a date in entered in the cell. 

 

ss3.PNG

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Try... This will check to see if the actual date is approved or not and leave the cell blank if it isn't. 

    =IF(ISBLANK([Floor Plan Approved]4), "", IF([Floor Plan Approved]4 < [Floor Plan Approved]3, "True", IF([Floor Plan Approved]4 > [Floor Plan Approved]3, "False", IF([Floor Plan Approved]4 = [Floor Plan Approved]3, "Blue"))))

    Did it work?