Expand my Community achievements bar.

Calculated Field- Embedded IF Statement

Avatar

Level 6
Hello- I am trying to calculate a expected percentage complete for each task biased solely on current date and duration of the task. I got this single IF statement to work: CONCAT(IF(($$NOW>Planned Start Date),((PROD(DIV((ABS(WEEKDAYDIFF($$NOW,Planned Start Date))),(DIV(Duration,480))),100))),0),"%") Now I need to add a criteria that if the current date is greater than the Planned Completion Date then put the value 100 in AND only do the calculation IF the task task duration is greater than 0 and if the task percent complete is not equal to 100. I've tried the following but something isn't adding up. IF(($$NOW>Planned Start Date, IF($$NOW 0,IF(Percent Complete<>100, PROD(DIV(ABS((WEEKDAYDIFF($$NOW,Planned Start Date))),(DIV(Duration,480))),100,0),100),0),100) Any help would be most appreciated! Thank you, Mollie Mollie Shatek JLL
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

4 Replies

Avatar

Level 8

I'm still not sure what you're trying to calculate, but I can give you a couple of pointers. For percent complete not equalling 0, you should use != for not equals, rather than <>. So it should read... IF(Percent Complete!=100, You also have 12 open brackets "(" and only 10 closing brackets ")" - for the correct syntax, you should have an even number of both. For example, if you remove the first IF statement with two opening brackets, the rest of the formula validates (although I don't know if it's calculating correctly). IF(($$NOW>Planned Start Date, IF($$NOW 0,IF(Percent Complete!=100, PROD(DIV(ABS((WEEKDAYDIFF($$NOW,Planned Start Date))),(DIV(Duration,480))),100,0),100),0),100) Barry Buchanan Work Management Australia0690z000007ZiPTAA0.png

Avatar

Level 6
Hi, thanks for the tip! Is there some place I can find a list of the logical expressions that can be used in workfront? I think I did get my formula working: IF(($$NOW>Planned Start Date),IF(ROUND((PROD(DIV((ABS(WEEKDAYDIFF($$NOW,Planned Start Date))),(DIV(Duration,480))),100)))>100,"100%",CONCAT(ROUND((PROD(DIV((ABS(WEEKDAYDIFF($$NOW,Planned Start Date))),(DIV(Duration,480))),100))),"%"))) It basically calculates a "expected percentage complete" biased solely on duration. So if a task is 10 days long you are expected to be 50% done at day 5. Thanks! Mollie Mollie Shatek JLL North America

Avatar

Level 8
If you go to the training options, advanced report writing is a very handy reference - for custom fields as well. You can book for a live course (where you can ask questions), watch a recording of a live course and/or download the courseware. Barely a day goes by where I don't refer to the courseware. Barry Buchanan Work Management Australia