Multiple If statements in text mode | Community
Skip to main content
BrandonNW
BrandonNW
Level 6
December 13, 2019
Question

Multiple If statements in text mode

  • December 13, 2019
  • 7 replies
  • 1610 views

Cheerio!!

We are calculating cycle times using project statuses and I need to account for all of the variables that can happen in a project. i.e The time between status A and B or the time between status A and C or the time between status A and D, you get the picture....

I can account for one variable, but I am struggling with the code for multiple Ifs.

valueexpression=IF(ISBLANK({DE:Current WIP Date}),WEEKDAYDIFF({DE:WIP Design and Create Date},{DE:Completed Date}),WEEKDAYDIFF({DE:Current WIP Date},{DE:Completed Date}))

How can I add a secondary and third IF condition?

This is saying, If "Current WIP Date" is blank, show me the difference between "Design and Create Date" and "Completed Date", otherwise show me the difference between "Current WIP Date" and "Completed Date".

I want to add a condition (or several) that will also account for when both of those fields are blank.

For example: If "Current WIP Date" is blank, show me the difference between "Design and Create Date" and "Completed Date", otherwise show me the difference between "Current WIP Date" and "Completed Date". But, If "Current WIP Date" and "Design and Create Date" are blank, show me the difference between "Client Review Date" and "Completed Date".

Is this possible in text mode??

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    7 replies

    JasonMu
    JasonMu
    December 16, 2019

    Hi Brandon,

    I think you are looking for a nested if statement

    valueexpression=IF({project}.{DE:Final Files Due Date}>=0,IF({plannedCompletionDate}>={project}.{DE:Tactic Requested Due Date},"Needs Attention","On Time"),IF({plannedCompletionDate}>{project}.{DE:Final Files Due Date},"Needs Attention","On Time"))

    This checks to is if the field has something in it then drops to the correct if statement depending on the initial True or False

    If the first statement is true it runs the Bolded if statement if false it runs the italic if statement.

    Jason

    K
    KevinQu1
    Level 10
    December 16, 2019

    As Jason mentions, doing "or" with IFs involves nesting them. Sometimes is easier to do these in a text editor where you can bold or color-code each part of the nest so you do them correctly, or assemble each part of the nest separately and paste it altogether at the end.

    It can really be a visual mess if you get into things like 12-levels of nested IFs. Times like this I wish WF had true ELSEIF or CASE statements. Or just basic OR/AND to combine expressions, which would be a bit easier to look at on these complex single-line expressions. Or someone tells me I've been doing it the hard way all this time!

    BrandonNW
    BrandonNWAuthor
    Level 6
    December 17, 2019

    Appreciate the replies guys. I took a stab at this, but I am not getting very far. This is what I came up with, where am I failing here?

    valueexpression=IF({DE:Current WIP Date}>=0,IF(ISBLANK({DE:Current WIP Date},WEEKDAYDIFF({DE:WIP Design and Create Date},{DE:Completed Date}), WEEKDAYDIFF({DE:Current WIP Date},{DE:Completed Date}))), IF(ISBLANK({DE:WIP Design and Create Date}, WEEKDAYDIFF({DE:Client Review Date}, {DE:Completed Date}), WEEKDAYDIFF({DE:WIP Design and Create Date}, {DE:Completed Date}))))

    I am guessing maybe it's in the parens?? Or, maybe I am butchering this completely?? ;)

    K
    KevinQu1
    Level 10
    December 17, 2019

    If no one else looks into this in depth (no time here), some tips:

    Add columns to try out each part of the nest separately to see if any one chunk is a point of failure. So test each ISBLANK on it's own, each WEEKDAYDIFF, etc. Make sure each calculation works in and of itself.

    Then what I often do is assembly each IF statement separately with placeholders for the next level in the nest so that I can carefully paste the nested bits one by one. Helps me keep the parenthesis straight.

    I did notice one thing...not sure how picky WF is about the spaces you have after every comma...and you have said spaces in numerous places.

    BrandonNW
    BrandonNWAuthor
    Level 6
    December 17, 2019

    Good advice. That got me up and running for the first nested statement.

    valueexpression=IF({DE:Current WIP Date}>=0,IF(ISBLANK({DE:Current WIP Date}),WEEKDAYDIFF({DE:WIP Design and Create Date},{DE:Completed Date}), WEEKDAYDIFF({DE:Current WIP Date},{DE:Completed Date})),IF(ISBLANK({DE:WIP Design and Create Date}),WEEKDAYDIFF({DE:WIP Client Review Date},{DE:Completed Date}), WEEKDAYDIFF({DE:WIP Design and Create Date},{DE:Completed Date})))

    I got to figure out how to get the next nested IF in there, but that got me pointed in the right direction. Thanks guys!

    TamBou
    TamBou
    Level 5
    January 29, 2020
    Hi Brandon, am wondering if you were able to get this to work with the multiple nested statements. Tammie Bouchard National Safety Council
    BrandonNW
    BrandonNWAuthor
    Level 6
    January 29, 2020

    I did - sort of... lol. It ended up looking like this, but the calc breaks after the first two date fields for some reason.

    valueexpression=IF(ISBLANK({DE:Current WIP Date}&&{DE:WIP Design and Create Date}&&{DE:WIP Client Review Date}&&{DE:WIP Compliance Review Date}),WEEKDAYDIFF({DE:WIP Build and Delivery Date},{DE:Completed Date}),IF(ISBLANK({DE:Current WIP Date}&&{DE:WIP Design and Create Date}&&{DE:WIP Client Review Date}),WEEKDAYDIFF({DE:WIP Compliance Review Date},{DE:Completed Date}),IF(ISBLANK({DE:Current WIP Date}&&{DE:WIP Design and Create Date}),WEEKDAYDIFF({DE:WIP Client Review Date},{DE:Completed Date}),IF(ISBLANK({DE:Current WIP Date}),WEEKDAYDIFF({DE:WIP Design and Create Date},{DE:Completed Date}),WEEKDAYDIFF({DE:Current WIP Date},{DE:Completed Date})))))

    I talked with a consultant and he advised to work this from a different angle. I am now calculating the each of the dates separately and then summing them in a new column.

    SUM({DE:Current WIP Date Cycle},{DE:WIP Design and Create Date Cycle},{DE:WIP Client Review Date},{DE:WIP Compliance Review Date})

    This way I can pull the cycle times of each status the project is in and then SUM them all together to show the overall cycle time.

    Terms & ConditionsAccessibility statement

    Loading footer...