Forum Discussion
Help Creating Custom Field to Calculate Elapsed Duration to Compare Against Actual Work Duration
John-project Thanks for the revised formula. It appears however, it doesn't account for tasks that have not yet started, so the Elapsed Duration Field is displaying a #ERROR. I'll have to include a criteria that if Actual Start=NA then Elapsed Duration = 0, or something like that.
What I'm trying to do here is develop a "Work Process Efficiency" project performance KPI. For that I was taking Actual Duration/Elapsed Duration. Basically, the duration of actual work being performed compared to the time the task has been in an "In Progress" status. This is similar measuring a business or manufacturing process. Process Time is the actual touch time or time working on a task, whereas the lead time aka Elapsed Duration is cycle time+lead time. So maybe I need to use a custom field and formula or a different built-in field that represents "Work Duration" as it doesn't seem like the built in Actual Duration field is what I want to use, because the Work Duration should never exceed total elapsed duration. Any ideas?
You're right, my modified formula is lacking (I wrote it last night and was in too much of a hurry). This modified version addresses the issue:
IIf([% Complete]=0,0,IIf([% Complete]=100,round(ProjDateDiff([Actual Start],[Actual Finish])/480)*480,round(ProjDateDiff([Actual Start],[Status Date])/480)*480))
Yes, I did question the validity of work time, the formula isn't as simple as it may seem. I have some other things on my plate right now so I'll have to give it some thought and get back to you.
John
John-project Thanks for the formula update. Sure, if anything comes to mind, please let me know on how I can use MS Project data to calculate Work Process Efficiency as using the built-in Actual Duration field probably isn't the right field to use. I'll keep brainstorming in the mean time as well.
- John,
I have a break in the action (waiting for a phone call) so let me give my initial assessment.
Capturing the "process time" might ordinarily be defined as the time between the actual start and actual finish of a task but that assumes once work starts it is continuous, which is not always the case (i.e. "if one is able to work on it uninterupted").
The "lead time" is not readily available with any existing Project metrics. For example, what determines when the "work is made available"? That might be considered the Start date but the only way to "catch" the "work is idle" time between availability and actual start is by looking at the Start Variance and that assumes the Baseline Start is still valid (i.e. the schedule didn't shift forward or backward due to other factors).
Similarly the "idle time" after the work is completed might be defined by the Finish Variance field, but again, with assumptions. Or, the idle time after might simply be the lag time from actual finish to the start of the successor task. This lag might be embodied or included with normal non-working days (e.g. weekends).
So, given some assumptions you might be able to get there from here with the noted Project fields or perhaps a more meaningful "process efficiency" by creating two additional custom fields, one for the "work received" time and another for the "start of next process"
JohnJohn-project There are certainly many conditions/variables to consider when trying to come up with a work process efficiency KPI in MS Project. I got the whole idea from the PMBOK 7th edition that mentioned measuring process efficiency in terms of value-added (Working on a Task) versus non-value added (Task started but idle) time. I'm still thinking the easiest method to calculate a Work Efficiency KPI is taking Actual Duration / Elapsed Duration (total time from the tasks start to the status date). It may not be the most accurate but at least it doesn't make my head spin.
The obvious downside however, is that you demonstrated how its possible for Actual Duration to be greater than elapsed duration, which in reality can't happen. So if there was some way to solve for that. That would help.