Need IF statement help

I am trying to build a formula using Duration to auto-select one of four statuses. Below is the formula I'm trying to build:

If Duration is less than/equal to 30 days, then = "Transition" OR Duration is greater than 30 days and less than/equal to 60 days, then = "Reshaping", OR Duration is less than/equal to 90 days, then = "Alignment", OR Duration is greater than 90 days, then "Implementation"

I tried to build it using IF(Or statements but it is coming back as Unparseable. Any thoughts on how to build this expression efficiently?

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hi Alisa

Try this. As I thought about your problem, I substituted the word IF in my thinking, in place of your OR, to better clarify the different fragments of your equation.

=IF(Duration@row<=30,"Transition", IF(AND(Duration@row>=30, Duration@row<=60),"Reshaping",IF(Duration@row, <=90, "Alignment","Implementation")))

Alisa Buck

Thanks! This is giving me mixed results. It either results in "Transition" or Incorrect Argument. It is interesting that in one line the duration is 129d but it is resulting in Transition. Other lines where the duration is more than 30d, it is resulting in Incorrect Argument. This is still closer than I could get!

Alisa Buck

I take that back, I repasted it and it results in "Transition" for every line. So it isn't making it past the first IF statement.

Thoughts?

Kelly Moore

Oops try this.

=IF(Duration@row<30,"Transition", IF(AND(Duration@row>=30, Duration@row<=60),"Reshaping",IF(Duration@row, <=90, "Alignment","Implementation")))

I got rid of the first equal to.

Alisa Buck

Thanks! Closer still! It works where duration is 30 days or less, but still gives me an incorrect argument on lines where duration is more than 30 days.

Alisa Buck

I did some additional testing and it looks like it is ok with the first and second IF's and returns the correct result for less than 30 days and for between 30 & 60 days. It doesn't like it when the duration is greater than 60 days or greater than 90 days. In those cases, it gives me an Incorrect Argument result.

Alisa Buck

I changed the last half of the statement so that the 3rd IF is between 60 and 90 days. Now it gives me the correct results for the first and second IF but gives me an Invalid Data Type on all other lines. Below is the revised formula

=IF(Duration@row < 30, "Transition", IF(AND(Duration@row >= 30, Duration@row <= 60), "Reshaping", IF(AND(Duration@row, >60, Duration@row <= 90), "Alignment", "Implementation")))

Thoughts?

Debbie Sawyer

@Alisa Buck

Try

=IF(Duration@row<31,"Transition",IF(AND(Duration@row>30,Duration@row<61),"Reshaping",IF(AND(Duration@row>60,Duration@row<91),"Alignment","Implementation")))

Does this work?

Good luck

Debbie

Alisa Buck

Wow Debbie! You are a miracle worker! That fixed it! Thanks!

Thanks to KDM as well! I appreciate this community so much!!!!

Debbie Sawyer

You're welcome, to be honest, I started writing the formula before any other answers had been submitted, but then I took a zoom call and was with a client for 50 mins! I finished writing your formula and posted! I didn't see any of the thread at all!

Glad it worked! :D

PS just looking at some earlier formulas in the thread, there were some additional comma's which would have thrown up errors...

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.