Date Comparison Formula Help

Options

I am a SmartSheet novice, but my team is used to base tracking in SmartSheet. I would like to set up conditional formatting that highlights if a site's planned schedule puts the site at risk.

I would like to compare two columns: "Construction Schedule: Turnover to Retail Date" against "Exterior Installation: Scheduled Date".

If the Construction Schedule date is less than 14 Days after the Exterior Installation date, I would like for the checkbox to become active.

I don't understand enough about the formatting of formulas to make this happen.

Best Answer

  • Monique Odom-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Tartaruchus77,

    First, you'll want to create a Checkbox column that is not restricted to checkbox use only. Then, use the following formula:

    =IF([Construction Schedule: Turnover to Retail Date]@row < ([Exterior Installation: Scheduled Date]@row + 14), true)

    Convert it to a column formula, and then you'll be able to create a Conditional Formatting rule for when that column is checked!

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom-Stearn

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

Answers

  • Monique Odom-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Tartaruchus77,

    First, you'll want to create a Checkbox column that is not restricted to checkbox use only. Then, use the following formula:

    =IF([Construction Schedule: Turnover to Retail Date]@row < ([Exterior Installation: Scheduled Date]@row + 14), true)

    Convert it to a column formula, and then you'll be able to create a Conditional Formatting rule for when that column is checked!

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom-Stearn

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!