Forum Discussion
Help Creating Custom Field to Calculate Elapsed Duration to Compare Against Actual Work Duration
ignacio_martin_mvp I don't know if its the way I'm using the formula you provided is calculating Elapsed Duration using the difference between the status date and actual start date, but somehow, For one of my projects, For the project summary task circled in RED, I show an Actual Duration aka "Work Duration" that is greater than the Elapsed Duration as shown below. How is that possible? I wouldn't think that Actual Duration of project work could exceed the total elapsed duration or time that has passed since the project has been in progress. Any ideas?
I think the formula provided isn't quite what you requested. I believe this simplified formula meets your original requirements:
IIf([% Complete]=100,round(ProjDateDiff([Actual Start],[Actual Finish])/480)*480,round(ProjDateDiff([Actual Start],[Status Date])/480)*480)
As to your current question, why couldn't the calculated Elapsed Duration be greater than the Actual Duration? Actual Duration is driven by % Complete whereas Elapsed Duration is derived from the Status Date and the Status Date and % Compete are independent metrics. Take the following example of two 10 day tasks starting on Monday. The Status Date is set as the first Friday in the two week span. Note the difference in Actual Duration compared to the Elapsed Duration for each of the tasks.
Make sense?
John
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?
- John,
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.
JohnJohn-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.