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

Alycia Washington

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

Genevieve P.

Hi @Alycia Washington

Based on that image, it looks like your Date Notice Due column isn't set up as a date type of column (see here), so the formula isn't able to recognize the text as a date to evaluate it against TODAY(). I get the same INVALID error if I try to reference a text type of column.

How is this data (the Date Notice Due) being created? Is it manually input or returned from a formula?

Paul Newcome

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

Alycia Washington

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

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.

Alycia Washington

Tried that and still no luck.  Any other suggestions?

Genevieve P.

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

Genevieve P.

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

Alycia Washington

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.

Genevieve P.

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?

Alycia Washington

Genevieve P.

Hi @Alycia Washington

Based on that image, it looks like your Date Notice Due column isn't set up as a date type of column (see here), so the formula isn't able to recognize the text as a date to evaluate it against TODAY(). I get the same INVALID error if I try to reference a text type of column.

How is this data (the Date Notice Due) being created? Is it manually input or returned from a formula?

Alycia Washington

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

Genevieve P.

Great! So glad we could figure it out 🙂

Alycia Washington

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?

Genevieve P.

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