IF(AND not working as expected
Greetings,
I have a project with Start/End date (Type: Date/Time) column, Status column (Type: Dropdown), along with a project health column (Type: Symbol). I am looking to control the color of the harvey balls from Gray, Red, Yellow, and Green depending on Date AND status such as
If the status is complete, then gray
If the start date is within 7 days AND the status is Not Started, then Yellow
If the End Date is in the past AND status is not yet Complete, then Red
This works fine and what's in there now:
=IF(Status@row = "Complete", "Gray", IF([Start Date]@row >= TODAY(+14), "Green", IF([Start Date]@row >= TODAY(+7), "Yellow", "Red")))
The following gives me a "Invalid Data Type" error:
=IF([Status]@row = "Complete", "Gray", IF(and([Start Date]@row >= TODAY(+7), Status@row = "Not Started", "Yellow"), If(and([End Date]@row >= Today(), status@row <> "Complete", "Red"), "Green")))
Any help is appreciated
Best Answer
-
@AJCruz I actually checked into this more and I recommend using this formula to get the results you are looking for. =IF(Status@row = "Complete", "Gray", IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(AND([Start Date]@row <= TODAY(7), Status@row = "Not Started"), "Yellow", "Green"))))
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
Answers
-
Remove the + from the TODAY(+7). it automatically calculates 7 days into the future when entering a positive number.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
Thank you Melissa,
Still getting "invalid data type" error after change which is odd since there is a current nested IF statement already there and just adding an additional condition.
Formula as entered:
=IF([Status]@row = "Complete", "Gray", IF(and([Start Date]@row >= TODAY(7), Status@row = "Not Started", "Yellow"), If(and([End Date]@row >= Today(), status@row <> "Complete", "Red"), "Green")))
-
@AJCruz Your parenthesis are incorrect. Your last "and" statement should have a closing parenthesis after "Complete". Try that then let me know if you still have trouble
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
@AJCruz I actually checked into this more and I recommend using this formula to get the results you are looking for. =IF(Status@row = "Complete", "Gray", IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(AND([Start Date]@row <= TODAY(7), Status@row = "Not Started"), "Yellow", "Green"))))
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
-
Woops, looks like our updates crossed
I'll try that
-
@AJCruz Did you see the formula I just added? Try using that. You have many parenthesis issues in there. Here it is again.. =IF(Status@row = "Complete", "Gray", IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(AND([Start Date]@row <= TODAY(7), Status@row = "Not Started"), "Yellow", "Green"))))
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
Thank you Melissa!
Like a charm. Much appreciated
-
@AJCruz Your very welcome! Please be sure to mark my response as the answer to your question and have a wonderful rest of your day!
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!