Forum Discussion
Help Creating Custom Field to Calculate Elapsed Duration to Compare Against Actual Work Duration
You can try the following formula, in a custom duration field, such as Duration1, for example:
IIf([Actual Finish]<[Status Date];round(ProjDateDiff([Actual Start];[Actual Finish])/480)*480;IIf([Actual Start]<[Status Date];round(ProjDateDiff([Actual Start];[Status Date])/480)*480;0))
Regards
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?
ignacio_martin_mvp Yes, I believe this formula works. I got a syntax error at first because you used semi colons in the formula so I had to replace those with commas. I can now see that a tasks baseline duration was supposed to be 20 days and is 25% complete, however the actual elapsed duration or time its been in an In Progress status shows 36 days. Thank you for your help.
