I am trying to create a formula that determines when date is within 30,60,90 days from todays date.

I am trying to create a formula that determines when date is within 30,60,90 days from today's date. The value if true will return the red, blue or green icons. The formula works if I only include"= but it does not work and returns Invalid Operations if I use <, >, <= This is the formula I entered:

=IF(TODAY(30) >= [Date Notice Due]2, Red, IF(TODAY(-60) = [Date Notice Due]2, Yellow, IF(TODAY(-90) = [Date Notice Due]2, Green, Notice Date Over 90 Days))).

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your formula posted above is missing quotes around each of the colors.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Quotes are present on the formula, not sure why it didn't come through on the original post.

    This is what I am trying to accomplish. Which brings back invalid operation error.

    =IF(TODAY(-30) <= [Date Notice Due]1, "Red", IF(TODAY(-60) <= [Date Notice Due]1, "Yellow", IF(TODAY(-90) <= [Date Notice Due]1, "Green", "Notice Date Over 90 Days")))

    If I enter the formula as listed below it works and brings back the "Notice Date Over 90 Days"

    =IF(TODAY(30) = [Date Notice Due]1, "Red", IF(TODAY(-60) = [Date Notice Due]1, "Yellow", IF(TODAY(-90) = [Date Notice Due]1, "Green", "Notice Date Over 90 Days"))).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sometimes Smartsheet can get a little finicky when comparing dates like this. Try removing the formula, logging out, clearing your browser's cookies and cache, logging back in, then manually retyping the formula.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Tried that and still no luck.  Any other suggestions?

  • Hi @Alycia Washington

    When you use the negative sign in TODAY, it will be looking in the Past... so for example, TODAY(-30) is looking back in the past 30 days, instead of looking within the next 30 days, which would be TODAY(30).

    Try this:

    =IF(TODAY(30) <= [Date Notice Due]@row, "Red", IF(TODAY(60) <= [Date Notice Due]@row, "Yellow", IF(TODAY(90) <= [Date Notice Due]@row, "Green", "Notice Date Over 90 Days")))

    I also replaced your row references with @row so that the formula knows it only needs to look in the Date Notice Due cell that's within this current row.

    Let me know if this works! If it doesn't, it would be helpful to see a screen capture of how your sheet is set up (but please block out any sensitive data).

    Thanks,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Actually, in taking another look at the formula, I would reverse your order of criteria so it reads "If the Date Notice Due is less than 30 days from now" instead of "if 30 days from now is less than the Date Notice Due"

    Try this?

    =IF([Date Notice Due]@row <= TODAY(30), "Red", IF([Date Notice Due]@row <= TODAY(60), "Yellow", IF([Date Notice Due]@row <= TODAY(90), "Green", "Notice Date Over 90 Days")))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Genevieve - I tried your recommendation and unfortunately, still received the "Invalid Operation" error. I attached a screenshot of my sheet - not sure if this is helpful.

  • Hi @Alycia Washington

    I don't think the screen capture came through - would you mind trying again?

    Could you also make sure that the Date Notice Due is formatted as a Date Type of column?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • That was it - THANK YOU so much for your help.

  • Great! So glad we could figure it out 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • After solving my initial issue - I realized there is one additional factor I need to consider in my formula. I need the formula to look at column "Date Notice Submitted" and exclude any line items that have a date. Do you know of a way to incorporate this functionality into the formula?

  • Hi @Alycia Washington

    No problem! You can add in another statement at the beginning to say "If the Date Notice Submitted column is not blank, return a blank cell". Otherwise...do the formula we created above.

    Initial statement:

    =IF([Date Notice Submitted]@row <> "", ""

    Added to your formula,

    =IF([Date Notice Submitted]@row <> "", "", IF([Date Notice Due]@row <= TODAY(30), "Red", IF([Date Notice Due]@row <= TODAY(60), "Yellow", IF([Date Notice Due]@row <= TODAY(90), "Green", "Notice Date Over 90 Days"))))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!