Multiple If statements in text mode

[BrandonNW]

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??

[JasonMu]

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

[CalvinFold]

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]

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?? ;)

[CalvinFold]

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]

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!

[TamBo64]

Hi Brandon, am wondering if you were able to get this to work with the multiple nested statements. Tammie Bouchard National Safety Council

[BrandonNW]

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.