# 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))).

• ✭✭✭✭✭✭

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

• 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"))).

• ✭✭✭✭✭✭

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.

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

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

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")))

• 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.

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?

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

Great! So glad we could figure it out 🙂

• 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?

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 <> "", ""