Leave field Blank when using IF formula, on Date Row.

Options
mmota
mmota ✭
edited 02/16/24 in Formulas and Functions

Hello,

I would be eternally grateful if someone can help me, I have tried ISBLANK, and "" to leave the collum's empty but its not working.

I have the IF formula to turn Red when the date is nearing 30 days, Yellow when the date is nearing 60 days, and Green when the date is over 60 days. But some fields will be blank; on the blank fields its automatically showing RED. And I need it to just be blank if there is not a date in that field, but I need the formula to still be for the whole column because we will continue to add to the sheet.

Our actual sheet has a lot of information in between the dates like addresses so the "expiration date" column will be empty and now its showing a lot of red everywhere and its throwing off the purpose of the spreadsheet.

=IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row > TODAY(+90), "Green")))

image.png


· Share on FacebookShare on Twitter

Best Answers

  • Amanda Carta
    Amanda Carta ✭✭✭
    Answer ✓
    Options

    add an "IF" statement to the formula. I know you said you tried it but I found I didn't close it out before adding the ,"" at the end.

    =IF(ISBLANK([Expire Date]@row), "")

    image.png


    · Share on FacebookShare on Twitter
  • Amanda Carta
    Amanda Carta ✭✭✭
    Answer ✓
    Options

    Just cover the rest with a >=60

    =IF(ISBLANK([Expire Date]@row), "", IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row >= TODAY(+60), "Green"))))

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!