Multiple IF AND OR in same formula

LLL

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

LLL

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.

Andrée Starå

Hi L,

What error message do you get?

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

LLL

The multiple cases do not function correctly , In progress and Overdue does not function ok. The Cancelled rule doesn`t seem to apply at all. Sorry i can`t share the data.

Andrée Starå

@LLL

Ok. Are they in the right order? It goes from the left and stops as soon something is true.

Make sense?

LLL

Please find a print screen

I hope this helps.

Thanks.

LLL

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.

Andrée Starå

@LLL

Excellent!

Glad you got it working!

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!