IF statement comparing today's date to a projected date
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"))
Best Answers
-
Try: =IF([2 Approval Package Current Projected Date]@row > TODAY(10), "Green", IF([2 Approval Package Current Projected Date]@row <= TODAY(), "Red", "Yellow"))
-
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.
Answers
-
Try: =IF([2 Approval Package Current Projected Date]@row > TODAY(10), "Green", IF([2 Approval Package Current Projected Date]@row <= TODAY(), "Red", "Yellow"))
-
Hello Nic! Using this formula I still get #INVALID OPERATION. Any ideas?
Thank you so much for trying to help. Much appreciated.
-
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.
-
I typed it in manually rather than copy + pasting and that worked! Thank you so much for your help, Nic!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!