IF statement comparing today's date to a projected date

gjohnson.pcpro
gjohnson.pcpro ✭✭✭
edited 11/02/23 in Formulas and Functions

Hello,

I have a column named 2 Approval Package Current Projected Date. I am trying to return a text value based on that column compared to today's date. Here's what I'm looking for:

If 2 Approval Package Current Projected Date has already passed, the text should say RED

If 2 Approval Package Current Projected Date is in the next 10 days, the text should say YELLOW

If 2 Approval Package Current Projected Date is more than 10 days out, the text should say GREEN

This is the formula I wrote to achieve this, but I am getting #INVALID OPERATION. Can a kind soul please take mercy and assist?

=IF([2 Approval Package Current Projected Date]@row < TODAY(), "Red", IF([2 Approval Package Current Projected Date]@row - TODAY() < 10, "Yellow", "Green"))

· Share on FacebookShare on Twitter

Best Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Try: =IF([2 Approval Package Current Projected Date]@row > TODAY(10), "Green", IF([2 Approval Package Current Projected Date]@row <= TODAY(), "Red", "Yellow"))

    · Share on FacebookShare on Twitter
  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Hmm. Is the date column setup as a date type column vs text/number?

    You can also just try retyping the formula vs copying/paste which has been known to cause issues in some cases.

    · 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!