Calculating the difference between Estimated Duration (Part of the Project Settings) and Actual.
Hi ,
Estimated Duration is part of the Project settings and drives the start and finish date.
I want to calculate the difference between estimate and actual . We want to track in hours. i.e .25h = 15 min.
The only way I can get the calculation to work is .25 days but that is really 2 hours, not 15 min.
Estimated Duration  Actual Duration= Duration Difference
How do I calculate the difference with keeping the Estimated and Actual in Hours?
Best Answer

0.25 days is actually 6 hours and not 2. 15 minutes is going to be about 0.01 days (actual is 0.010466667). If you know how many minutes the duration is going to be, then you can simply divide it by 1,440 to find out the decimal for how much of a day the duration is. This way you can have consistent data entry for each of the durations in days so that your difference calculations are correct.
You could build out a conversion formula to convert the estimated and/or the actual into consistent formats and run the calculations that way.
Something like a nested IF statement that looks for a "d" and divides by 1 but divides by 1,440 if it finds an "m". Write one for the estimated and a second for the actual and then subtract the one from the other.
=([Estimate Duration]@row / IF(FIND("d", [Estimate Duration]@row) > 0, 1, 1440))  [Actual Duration]@row
Or something along those lines.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

Multiply your actual by 60 to get the equivalent in hours/minutes.
Example: 0.25 * 60 = 15
Hope that helps.
Cheers,
Ramzi
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Thanks Ramzi, this is what happened.
I tried the math a few different ways, but could not get the right solution.
Any other ideas?
=([Estimate Duration]@row  ([Actual Duration]@row * 60))

@Paul Newcome Can you help me find a solution? Or can you tag me in something similar? I would appreciate it very much!

0.25 days is actually 6 hours and not 2. 15 minutes is going to be about 0.01 days (actual is 0.010466667). If you know how many minutes the duration is going to be, then you can simply divide it by 1,440 to find out the decimal for how much of a day the duration is. This way you can have consistent data entry for each of the durations in days so that your difference calculations are correct.
You could build out a conversion formula to convert the estimated and/or the actual into consistent formats and run the calculations that way.
Something like a nested IF statement that looks for a "d" and divides by 1 but divides by 1,440 if it finds an "m". Write one for the estimated and a second for the actual and then subtract the one from the other.
=([Estimate Duration]@row / IF(FIND("d", [Estimate Duration]@row) > 0, 1, 1440))  [Actual Duration]@row
Or something along those lines.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!