Calculated Field- Embedded IF Statement | Community
Skip to main content
M
MollieSh
Level 4
August 25, 2017
Question

Calculated Field- Embedded IF Statement

  • August 25, 2017
  • 4 replies
  • 927 views
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
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

4 replies

B
BarryBu2
Level 8
August 25, 2017

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 Australia

M
MollieShAuthor
Level 4
August 25, 2017
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
B
BarryBu2
Level 8
August 29, 2017
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
Terms & ConditionsAccessibility statement

Loading footer...