Multiple IF AND OR in same formula

Options
LLL
LLL
edited 06/09/20 in Formulas and Functions

Hello,

I`m trying to do something like this :

7 Status

IF(Action@row = "", "",

IF([% Complete]@row = 1, "Complete",

IF(Owner@row = "", "Not assigned",

IF(AND([End Date]@row = "", [% Complete]@row = 0), "Cancelled", 

IF(AND([% Complete]@row < 100, TODAY() > [End Date]@row), "Overdue",

IF(OR([% Complete]@row <> 0, [End Date]@row >= TODAY()), "In Progress",

IF(AND([% Complete]@row = 0, [End Date]@row >= TODAY()), "Not Started", 


The rules i need are:

  1. blank( "" ) if [Action]@row is blank
  2. "Complete" if [% Complete]@row is 100%
  3. "Not assigned" if the [Owner]@row is blank
  4. "Cancelled" if [End Date]@row = "" AND [% Complete]@row = 0%
  5. "Overdue" if [% Complete]@row <100% AND Today()>[End Date]@row
  6. "In Progress" if [% Complete]@row <> 0% OR [End Date]@row>= Today()
  7. "Not Started" if [% Complete]@row =0% AND current date is between start date and end date


Please help me understand what i`m doing wrong here ...


Thanks in advance for any help.


Regards. L

Best Answer

  • LLL
    LLL
    Answer ✓
    Options

    I think i`ve got it myself :


    =IF(Action@row = "", "", IF([% Complete]@row = 1, "Complete", IF(Owner@row = "", "Not assigned", IF(AND([% Complete]@row = "0", [End Date]@row = ""), "Cancelled", IF(AND([% Complete]@row = 0, [End Date]@row >= TODAY()), "Not Started", IF(AND([% Complete]@row < 100, TODAY() >= [End Date]@row), "In Progress", "Overdue"))))))


    This is giving me the results i was expecting.


    Please let me know if there is another way to do it .


    Thanks.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!