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
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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")))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That was it - THANK YOU so much for your help.
-
Great! So glad we could figure it out 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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 <> "", ""
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"))))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!