Formula to check star symbol question- Need Help!

Options

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))), "⭐", ""), "")

Tags:

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @lfloyd

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!