Calculating the difference between Estimated Duration (Part of the Project Settings) and Actual.

Tasha Garcia

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?

Paul Newcome

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.

Ramzi K

@Tasha Garcia

Multiply your actual by 60 to get the equivalent in hours/minutes.

Example: 0.25 * 60 = 15

Hope that helps.

Cheers,

Ramzi

Tasha Garcia

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

Tasha Garcia

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

Paul Newcome

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.