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.
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.
Help Article Resources
Categories
Check out the Formula Handbook template!