Help Creating Custom Field to Calculate Elapsed Duration to Compare Against Actual Work Duration

Iron Contributor

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:

19 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"))
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.

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

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"


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

How about this, instead of using the Actual Duration field, which is NOT the same as how you want to define "actual duration", use the delta between the Baseline Start and Status date. That will automatically pick up the idle time on the task start if there is any. If the baseline start is compromised, (i.e. no longer representative of "when" a task can/should start due to other factors), you could use the actual finish of the predecessor task as the "start availability". However, you can't do that with a custom field formula, VBA would be required.

Just a thought.

@John-project I’m not quite sure I’m following. You might have to provide a scenario where what you are suggesting would work to give me work duration using what you suggested. Also I hated programming in college so when you mentioned VBA I got nauseous.  I think my Elapsed Duration formula is good, but calculating the amount of work duration that occurred during the elapsed time until the task finishes is still unknown since it appears Actual duration is  so close, but not the answer obviously if its possible for that value to exceed the elapsed duration.


As I think more about how you are tying to apply a theoretical concept (i.e. lead time and process time) to Project, there just isn't a good fit. When creating a plan in Project the normal process is to create a logical sequence of tasks to accomplish an end goal. Tasks are linked using finish-to-start relationships with other variants (e.g. lead, lag, etc.) only used in very limited circumstances. As such, the plan doesn't provide any leading or lagging "dead" time, unless specifically pre-programmed into the plan (e.g. lead/lag in task relationships). When one task finishes, the next task starts so the conceptual lead time and real process time are the same.


With the above reality, the KPI for work efficiency will be 1 for all but perhaps a very few tasks. For covering those isolated tasks, (and I'm not sure why there is any benefit in doing so), the "work duration" (e.g. process time) is actual start to actual finish for completed tasks or actual start to status date for in-progress tasks. Lead time, including process time) would be the sum of predecessor lag (if any) plus process time plus successor lag (if any). I'll attach an example once my computer is finished booting up after a power glitch.


Update (after power glitch recovery):

While creating an example, it came to my attention that for in-process tasks, work duration is not actual start to the status date but rather, it is actual start to actual start plus actual duration.



@John-project Oh my this is getting complicated.  Still to me based on how I read the definition of Actual Duration and how it's calculated, that to me represents "process time"/touch time/work duration.


Actual Duration: The Actual Duration field shows the span of actual working time for a task so far, based on the scheduled duration and current remaining work or percent complete.

  • Actual Duration = Duration * Percent Complete

My custom field for Elapsed Duration which I would define as the span of total time passed so far based on the difference between the Actual Start of the task and the project status date.  This is where the ProjDateDiff formula comes in to calculate that difference (excluding non-working time as defined in the project calendar).

  • Elapsed Duration= Status Date-Actual Start

Again, the downside however of using the built-in  actual duration field and how it computes, its possible in the MS Project world anyways that the Actual Duration could exceed Elapsed duration, which in the real world we know isn't possible.  If I can solve for that, then I think I can calculate work process efficiency a little more realistically and accurately.  So I get my 3 project performance KPIs:  Cost, Schedule, and Work.   

@John-project Maybe to compensate for the potential of Work Duration exceeding elapsed duration, I could include an IIf condition in the custom work duration field where if Actual duration is greater than elapsed duration, then Work Duration equals Elapsed Duration.

You are definitely right, it gets complicated.

Again I reiterate, there is no relationship between Actual Duration and the Status Date, so other than using your "fudge" factor ( "iif" statement to tweak the results) you will never get Actual Duration to mold to what you want (i.e. you will never "solve for that").

Maybe you can simply things. How about you define the total duration (i.e. when task should start to when it should finish) as Baseline Duration plus and lags (if present). It includes wait times plus in-process work time. Then work duration is actual start to actual finish or actual start to status date, whichever applies.

Straightforward with no fudge factors needed.


@John-project So I ended up using the workaround where if the actual duration is greater than elapsed duration it sets the work duration value equal to the elapsed duration.  I don't think using baseline fields would be good because that's according to the "Plan", not what actually happens.  in measuring process performance, I need "Actuals".  As far as lags, I've never had the need to use lags in an agile project.  Most of my project tasks don't have predecessors/dependencies.  So, I don't see how lags would be applicable here.


I'm just going to stick with my Work Duration formula that uses actual duration with the condition that work duration can't exceed elapsed duration.  It may not be precise, but I think it will be good enough to measure work efficiency.

I question what kind of plan you have if most of your tasks don't have dependencies. Not even the normal finish-to-start?

Anyway, I think we've run this topic to the ground. If you got something you like, right or wrong, go with it.

@John-project  In agile projects tasks/aka requirements/aka user stories are supposed to be Independent, meaning it shouldn’t have any predecessors or successors.  However in practice it’s not always possible to meet the I-Independent for the story INVEST criteria.  Some requirements have to be implemented before others but not all.  When these instances occur then I establish the predecessor in project and JIRA has an option to select must be done before or must be done after and then you link it to the related requirement/story