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

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?

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!

I don't have an answer on the Delta, but if you adjust the formula for Actual duration, to be ActualStart, 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

Does this link work for you?
https://app.smartsheet.com/b/publish?EQBCT=b2d97eb8e1474f70a2af9d1e58d93f36

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:

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

Hi was following up to see if this ended up helping?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!