Help With Custom Field to Calculate Duration Variance

Iron Contributor

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?


4 Replies

@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
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.


@John-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.


You're right, I must have attached my comment to the wrong post. Furthermore, my "conceptual formula" was incomplete. It should have been:

Forecast work by status date = (Status date - Actual Start) * Work per time unit

where Work per time unit = Work/Duration


Using that construction there is no need to worry about time units (minutes, hours, or days) since they cancel out leaving a pure work unit of minutes, which is the native unit of work and time in Project. To put it in an actual custom field formula, it would look like this:

Forecast work by status date (Text1) = ProjDateDiff([Actual Start],[Status Date])*[Work]/[Duration]/60 & " hrs".

As noted previously, this works for normal linearly loaded work (flat work contour). A more complex calculation using VBA would be needed to accurately perform the calculation if work is contoured (e.g. front loaded, early peak, bell, etc.)


Here's an example of a simple plan with two tasks. A single resource works full time on "task a" and a single resource works half time on "task b". The Status Date is set as 9/2/22. Both resources logged 10 hours of actual work by end of shift on the status date. Note that "task a" is 14 hrs "behind" forecast but "task b" is 8 hrs "ahead" of forecast (meaning the resource assigned to "task b" actually worked more than his assigned half time).


The basic formula can be embellished to only apply to in-progress tasks.


Don't make this more complex than it needs to be by trying to work with duration.