Forum Discussion
Help With Custom Field to Calculate Duration Variance
I'm trying to create a custom duration variance field instead of the built in Duration variance field as the built-in duration variance uses Duration - Baseline Duration and Duration is based on a "Planned" duration still. I want to know duration variance based on ACTUALS for In Progress and Finished Tasks in order to determine whether individual tasks as well as the overall project are ahead, on, or behind schedule. So I came up with the following formula, but for some reason I keep getting a syntax error and I don't know why. The formula logic first checks if a task is finished, if its finished, then it takes the Baseline Duration-Elapsed Duration. NOTE: Elapsed Duration is another custom field I created that captures the total duration that a task was considered "In Progress" based on the status date rather than Actual Duration, which is calculated in part based on work performed, and doesn't account for time that has passed where the task may not have been being worked on consistently since it started. Then the formula checks if a task has started but not finished, if true, then it calculates duration variance between the Actual Duration, and Elapsed Duration. Finally, if none of the other conditions are met, then that means the task has not started and duration variance is set to 0.
IIf([% Complete]=100, round ([Baseline Duration]- [Elapsed Duration]), IIf([% Complete]>0 AND<100,round([Actual Duration]-[Elapsed Duration]),0)
What is wrong with my formula that project doesn't like the syntax?
JBLT-77 I think I solved for the formula syntax error because I got this formula to work:
IIf([% Complete]=100,round([Baseline Duration]-[Elapsed Duration]),IIf([% Complete] Between 0 And 100,round([Actual Duration]-[Elapsed Duration]),0))
So this should mean:
- A positive variance indicates ahead of schedule.
- A 0 variance indicates on schedule
- Negative variance indicates behind schedule
- John,
Here's my two cents.
The term "forecast work" may be defined in a couple of ways. Based on the original plan, forecast work would simply be baseline work, work that was forecast (planned) for the task. Or, in your way of thinking (I believe) you want forecast work to be the "current" forecast (i.e. based on actual start rather than "forecast" (baseline start) which may be different if the task started earlier or later.
I'm not sure why you want to attempt to user assignment units or max units, neither of those are relevant to task work which is an aggregate of all resource work on a given task. I suggest the following approach (expressed conceptually). It assumes a linear spread of work. If work is contoured, the formula will have to be calculated using VBA since timescaled work data is needed.
Forecast work by status date = (Status Date - Actual Start) * Work
Then compare this to actual work.
JohnJohn-project I think your reply here is related to my other discussion post topic Need Help Creating Formula For Custom Field To Calculate Forecasted Work Complete. and not this topic.
To try and simplify what I'm trying to do is create a custom project field that tells me what either the % Complete should be or the Work Complete should be based on the status date, so I can compare that to the actual % Complete and or Actual Work as of the status date.
Are you saying I'd have to create a Macro and write VBA to calculate a Forecasted Work Complete? Or I could just put the formula you mentioned into a number type custom field formula something like ProjDateDiff([Actual Start], [Status Date] * Work). Then there is the whole conversion piece I would need to figure out because part of the formula gives me the duration which would be in days, and then there is work which is in hours. So my formula ends up being something like ProjDateDiff(([Actual Start], [Status Date] /8) * Work). So the duration converted to hours multiplied by the planned work in hours should result in the amount of work that should be completeI'm not quite sure how to proceed here.
