Different dates for Status RYGG
Hi!
I have been pulling my hair out over this. I have gotten each part to work independently, but not together. We are trying to get the status to change if two separate dates are past due, or 30 days from coming due. We also have a column that would select the gray status if it is selected. Below is what I had written. Any advice/suggestions would be greatly appreciated!
=IF(AND([COI Expiration]@row - TODAY() > 30), "Green", IF([COI Expiration]@row < TODAY()), "Red", "Yellow", IF(AND([Contract Expiration]@row - TODAY() > 30), "Green", IF([Contract Expiration]@row < TODAY()), "Red", "Yellow", IF(AND([Do Not Use - Safety Concern]@row = 1), "Gray")))
Best Answer
-
Hi @bmclark
Can you confirm that your columns are set to Date Type of columns?
[COI Expiration] and [Contract Expiration] will both need to be Date Type columns in order for the formula to use the TODAY function.
If they are set to Date, try each statement individually to see where the error may be occurring:
=IF([Do Not Use - Safety Concern]@row = 1, "Gray")
=IF(AND(([COI Expiration]@row - TODAY()) > 30, ([Contract Expiration]@row - TODAY()) > 30), "Green")
=IF(AND([COI Expiration]@row < TODAY(), [Contract Expiration]@row < TODAY()), "Red")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi bmclark,
Try this:
=IF([Do Not Use - Safety Concern]@row = 1, "Gray", IF(AND( ([COI Expiration]@row - TODAY()) > 30, ([Contract Expiration]@row - TODAY()) > 30), "Green", IF(AND([COI Expiration]@row < TODAY(), [Contract Expiration]@row < TODAY()), "Red", "Yellow")))
Gia Thinh Technology - Smartsheet Solution Partner.
-
It is still coming up as UNPARSEABLE.
-
Apologies, it came up as invalid operation, not unparseable.
-
Hi @bmclark
Can you confirm that your columns are set to Date Type of columns?
[COI Expiration] and [Contract Expiration] will both need to be Date Type columns in order for the formula to use the TODAY function.
If they are set to Date, try each statement individually to see where the error may be occurring:
=IF([Do Not Use - Safety Concern]@row = 1, "Gray")
=IF(AND(([COI Expiration]@row - TODAY()) > 30, ([Contract Expiration]@row - TODAY()) > 30), "Green")
=IF(AND([COI Expiration]@row < TODAY(), [Contract Expiration]@row < TODAY()), "Red")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That was the issue. Thanks for your help!
-
No problem 🙂
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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!