# How to get the Delta between Estimated Duration and Actual Duration

✭✭✭✭

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.

Tags:

• 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?

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

• ✭✭✭✭

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?

• edited 03/07/23

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

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

• 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

• ✭✭✭✭

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

• ✭✭✭✭

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

• ✭✭✭✭

No, I get this error

• ✭✭✭✭✭✭

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:

• edited 03/08/23

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

• @Krista Coffman

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!