I need to use an IF / THEN formula to compare a cell from one column to a cell in another column

I could really use some help! Thank you in advance!

My STATUS column has the following colored options available per cell: Red, Yellow, Green and Blue.

I am looking to use the following IF / THEN statements:

1.  In STATUS, if the DELIVERY DATE is within SEVEN (7) DAYS of the START DATE, the LIGHT is RED. Can the DELIVERY DATE cell color change to RED as well?

2.  In STATUS, if the DELIVERY DATE is within FOURTEEN (14) DAYS of the START DATE, the LIGHT is YELLOW. Can the DELIVERY DATE cell color change to YELLOW as well?

3.  In STATUS, if the DELIVERY DATE is within TWENTY-ONE (21) DAYS of the START DATE, the LIGHT is GREEN. Can the DELIVERY DATE cell color change to GREEN as well?

4.  In STATUS, if the DELIVERY DATE is within TWENTY-EIGHT (28) DAYS of the START DATE, the LIGHT is BLUE. Can the DELIVERY DATE cell color change to BLUE as well?

My DATE STATUS column has the following in a drop down menu options available per cell: Hard Date, Scheduled Date, Placeholder Date

I am also trying to use the following statements to change the color of the duration bar in my Gannt view as well as my calendar view:

1.  In DATE STATUS, if HARD DATE is selected, the calendar DURATION BAR is BLUE.

2.  In DATE STATUS, if SCHEDULED is selected, the calendar DURATION BAR is YELLOW.

3.  In DATE STATUS, if PLACEHOLDER is selected, the calendar DURATION BAR is GREEN.

Thank you!

Geoff

Best Answer

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓

    Choose the conditional formatting option on the top of the page and you will have options to set this up exactly the way you have them typed up. When selecting the column for status and setting the condition to "Green", simply choose the format to highlight the cell green for "Delivery Date". The calendar should also display the formats as well.

Answers

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓

    Choose the conditional formatting option on the top of the page and you will have options to set this up exactly the way you have them typed up. When selecting the column for status and setting the condition to "Green", simply choose the format to highlight the cell green for "Delivery Date". The calendar should also display the formats as well.

  • Thank you! I was over-thinking this and missed the "next step" if you will.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!