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 [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: * blank( "" ) if [Action]@row is blank * "Complete" if [% Complete]@row is 100% * "Not assigned" if the [Owner]@row is blank * "Cancelled" if [End Date]@row = "" AND [% Complete]@row = 0% * "Overdue" if [% Complete]@row [End Date]@row * "In Progress" if [% Complete]@row <> 0% OR [End Date]@row>= Today() * "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

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:

blank( "" ) if [Action]@row is blank
"Complete" if [% Complete]@row is 100%
"Not assigned" if the [Owner]@row is blank
"Cancelled" if [End Date]@row = "" AND [% Complete]@row = 0%
"Overdue" if [% Complete]@row <100% AND Today()>[End Date]@row
"In Progress" if [% Complete]@row <> 0% OR [End Date]@row>= Today()
"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

Find more posts tagged with

Accepted answers

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.

All comments

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 :

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.