Need Help Creating Custom Field To Calculate Elapsed Duration For Task


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:

3 Replies
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"))
best response confirmed by JBLT83 (Contributor)
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))


@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.