Formula to check star symbol question- Need Help!
Hi friends, I need a smartsheet formula to check a star if any of the date columns are within the next 90 days AND if any of the text column values contain "text - value" in them. I have been using generative AI to assist me but something is slightly off an I keep getting an invalid operation error. below is the formula:
=IF(AND(OR([Date Column 1]@row <= TODAY() + 90, [Date Column 2]@row <= TODAY() + 90, [Date Column 3]@row <= TODAY() + 90, [Date Column 4]@row <= TODAY() + 90), OR(AND(TextColumn1@row = "text - value", TextColumn1@row <> "", [Date Column 1]@row <= TODAY() + 90), AND(TextColumn2@row = "text - value", TextColumn2@row <> "", [Date Column 2]@row <= TODAY() + 90), AND(TextColumn3@row = "text - value", TextColumn3@row <> "", [Date Column 3]@row <= TODAY() + 90), AND(TextColumn4@row = "text - value", TextColumn4@row <> "", [Date Column 4]@row <= TODAY() + 90))), IF(OR(NOT(ISBLANK(TextColumn1)), NOT(ISBLANK(TextColumn2@row)), NOT(ISBLANK(TextColumn3@row)), NOT(ISBLANK(TextColumn4@row))), "⭐", ""), "")
Answers
-
See below, not sure if this fits exactly what you are looking for...
=IF(AND(OR(AND(ISDATE([Date Column 1]@row), [Date Column 1]@row <= TODAY(90)), AND(ISDATE([Date Column 2]@row), [Date Column 2]@row <= TODAY(90)), AND(ISDATE([Date Column 3]@row), [Date Column 3]@row <= TODAY(90)), AND(ISDATE([Date Column 4]@row), [Date Column 4]@row <= TODAY(90))), OR(CONTAINS("text-value", [TextColumn1]@row), CONTAINS("text-value", [TextColumn2]@row), CONTAINS("text-value", [TextColumn3]@row), CONTAINS("text-value", [TextColumn4]@row))), 1, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!