Forum Discussion
Help Creating Custom Field to Calculate Elapsed Duration to Compare Against Actual Work Duration
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
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
- John,
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 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.
- John,
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 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.
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.