How to get the Delta between Estimated Duration and Actual Duration

Krista Coffman

Good Morning,

We are trying to move our deployment plan for technical project implementations into Smartsheet. What I'm struggling with is getting the delta between estimated duration and actual duration to account for working days only. My start date/time, end date/time and actual finish date/time are all manual input. The duration columns are formulas.

The formula for duration minutes is =(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))) * 60

The formula for Actual duration Minutes is =(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Actual Finish Date]@row) * 24)) - (VALUE(LEFT([Actual Finish Time]@row, FIND(":", [Actual Finish Time]@row) - 1)) + (VALUE(RIGHT([Actual Finish Time]@row, 2)) / 60))) * 60

I have zero idea how to get to a delta formula or if there is an easier way to do this.

In addition if anyone knows how to get dates and times into the same column and DD:HH:MM in the same column where all these items work together that would be greatly appreciated.

LadyHerbie

Hi -

Are you still looking for a solution on this? I'm trying to understand why your actual duration is the variance between Actual End Time and Estimated End time, rather than the variance between your actual finish time and your start time?

If you did find a solution can you post it here?

LadyHerbie

Would a solution like this to get all your data in one cell work?

These work off of all your existing fields, which now function as helper rows to populate these. you could theoretically hide your input/helper rows.

Krista Coffman

Hi I did not find a solution. It is likely I was over thinking the the variance between my actual finish time and my start time. However I was told a requirement would be to see the overall difference in time between estimated and actual. Did you use concatenation to get the above photo?

LadyHerbie

Yes I used the Join function to for the Start, Est End and Actual End times, i.e.:

=JOIN([Start Date]@row:[Start Time]@row, " ")

In order to get the calculation to assemble into d:h:m

=(ROUNDDOWN([Est Duration Minutes]@row / 1440, 0)) + "d" + " " + (ROUNDDOWN(([Est Duration Days]1 - (ROUNDDOWN([Est Duration Minutes]1 / 1440, 0))) * 24, 0)) + "h" + " " + ([Est Duration Minutes]1 - (((ROUNDDOWN([Est Duration Minutes]1 / 1440, 0)) * 1440) + (((ROUNDDOWN(([Est Duration Days]1 - (ROUNDDOWN([Est Duration Minutes]1 / 1440, 0))) * 24, 0)) * 60))) + "m")

(note I had changed the column headers on my sheet by adding "Est" to your existing columns.

Might be a long way around but it seems to work!

LadyHerbie

I don't have an answer on the Delta, but if you adjust the formula for Actual duration, to be Actual-Start, this is what it would look like

Krista Coffman

Which column does this formula data come from? I'm trying to figure out how to adjust it so it will work.

In order to get the calculation to assemble into d:h:m

=(ROUNDDOWN([Est Duration Minutes]@row / 1440, 0)) + "d" + " " + (ROUNDDOWN(([Est Duration Days]1 - (ROUNDDOWN([Est Duration Minutes]1 / 1440, 0))) * 24, 0)) + "h" + " " + ([Est Duration Minutes]1 - (((ROUNDDOWN([Est Duration Minutes]1 / 1440, 0)) * 1440) + (((ROUNDDOWN(([Est Duration Days]1 - (ROUNDDOWN([Est Duration Minutes]1 / 1440, 0))) * 24, 0)) * 60))) + "m")

Krista Coffman

I also don't know which formula you're saying to adjust to have actual start. Is it this one? It is also unclear what specifically needs to be adjusted in the formula to get the correct duration.

The formula for Actual duration Minutes is =(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Actual Finish Date]@row) * 24)) - (VALUE(LEFT([Actual Finish Time]@row, FIND(":", [Actual Finish Time]@row) - 1)) + (VALUE(RIGHT([Actual Finish Time]@row, 2)) / 60))) * 60

LadyHerbie

Does this link work for you?

https://app.smartsheet.com/b/publish?EQBCT=b2d97eb8e1474f70a2af9d1e58d93f36

Krista Coffman

No, I get this error

Ray Lindstrom

@Krista Coffman

Hi there. I use Target Start and End columns as well as Actual Start and End columns. In addition I created an Actual Duration column. Lastly I have a Duration Variance column to help understand the difference (delta).

I use this formula to grab the Actual Duration:

=IFERROR(NETWORKDAYS([Actual Start Date]@row, [Actual End Date]@row), "")

I use this to grab the Duration Variance:

=IFERROR(IF([Target Duration]@row > [Actual Duration]@row, ABS([Target Duration]@row - [Actual Duration]@row) + " days < Target", IF([Target Duration]@row < [Actual Duration]@row, ABS([Target Duration]@row - [Actual Duration]@row) + " days > Target", IF([Target Duration]@row = [Actual Duration]@row, "No Variance", ""))), "")

The variance column results look like this:

LadyHerbie

@Krista Coffman Sorry about that - doesn't look like I have permissions to share outside my org. this is what my revised sheet looks like - I've populated it into EXCEL for you.

Delta_Shared.xlsx

LadyHerbie

@Krista Coffman

Hi was following up to see if this ended up helping?