Forum Discussion
Help Creating Custom Field to Calculate Elapsed Duration to Compare Against Actual Work Duration
I created a custom text field called Elapsed Duration as I want to use this field instead of the built-in "Actual Duration" field which calculates based on % complete. I want to monitor and know the true duration that a task has been in progress even when work is not performed. Elapsed duration would be the amount of time that has passed between the Actual Start and Project Status Date for tasks that have not finished or for completed tasks amount of time that has passed between Actual Start and Actual Finish of the task. Then I want the Elapsed Duration rounded to whole days. I'm not sure what function in project to use for that. I'm not sure how to build complex formula like this for Elapsed Duration. Below is what I've come up with so far for individual functions.
Calculate Elapsed Duration for Tasks That have started but not finished:
IIf([Actual Duration]=0,"NA",ProjDateDiff([Actual Start],[Status Date])/480 & " " & "days")
Calculate Elapsed Duration for Tasks that have finished:
IIf([% Complete]=100,ProjDateDiff([Actual Start],[Actual Finish])/480 & " " & "days")
Round Duration To Whole Days:
?????
- This was my first attempt to build nested custom field functions, but its generating a #ERROR
IIf([Actual Duration]=0,"NA",IIf([Actual Duration]>0,ProjDateDiff([Actual Start],[Actual Finish])/480 & " " & "d",ProjDateDiff([Actual Start],[Status Date])/480 & " " & "d")) - Hi Juan
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))
Regardsignacio_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.
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
