Forum Discussion
Help With Custom Field to Calculate Duration Variance
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
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.
John