Calculating elapsed time in percentage

Linda F

I am looking for a formula to help me with elapsed time in % complete based on date columns. This is the formula I have used and the name of columns. In excel we use DATEDIF but I have tried different ways of doing it. It may be I don't need all the columns. Please help.

=IF((COUNTIF([Projected Start Date]3:[Step Complete]3, 1) / COUNT([Projected Start Date]3:[Step Complete]3)) = 1, "Full", IF((COUNTIF([Projected Start Date]3:[Step Complete]3, 1) / COUNT([Projected Start Date]3:[Step Complete]3)) > 0.75, "Three Quarter", IF((COUNTIF([Projected Start Date]3:[Step Complete]3, 1) / COUNT([Projected Start Date]3:[Step Complete]3)) > 0.5, "Half", IF((COUNTIF([Projected Start Date]3:[Step Complete]3, 1) / COUNT([Projected Start Date]3:[Step Complete]3)) > 0.25, "Quarter", "Empty"))))

Columns created:

Projected Start Date

Projected Finish Date

Actual Start Date

Actual Finish Date

Paul Newcome

Try something like this...

This should give you the percentage you SHOULD be at.

=(TODAY() - [Projected Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row)

And this should give you where you currently are (with it capping out at 100%):

=IF((TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row))

Paul Newcome

If there is an ACTUAL finish date, then the project is 100% complete. Since we already had to evaluate for going over 100%, working that in would be redundant. If you feel more comfortable working that in, we would use it to replace the "greater than 100%" portion of the second formula like so...

=IF((TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row))

=IF([Actual Finish]@row <> "", 1, (TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row))

It is shorter, but I had already typed up the portion to calculate the actual % complete. I saved keystrokes by simply copy/pasting that portion into the IF. So manually typing it all out, the [Actual Finish] saves keystrokes, but because I built the formula out of order, it was easier for me to just copy/paste instead.

Paul Newcome

Actually I just realized that the base formula we started with should have already been capping it at 100%.

=IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))

The problem is that you are entering an Actual Start Date in the future. That's where the negative numbers are coming from. Let's try this then...

=IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) <= 0, 0, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row)))

And then the IFERROR:

=IFERROR(IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) <= 0, 0, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))), "")

Paul Newcome

Try something like this...

This should give you the percentage you SHOULD be at.

=(TODAY() - [Projected Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row)

And this should give you where you currently are (with it capping out at 100%):

=IF((TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row))

Linda F

@Paul Newcome It worked !!! Thank you ! I noticed that we didn't use the Actual Finish Date, so I believed the finish date impacted the percentage?

Paul Newcome

If there is an ACTUAL finish date, then the project is 100% complete. Since we already had to evaluate for going over 100%, working that in would be redundant. If you feel more comfortable working that in, we would use it to replace the "greater than 100%" portion of the second formula like so...

=IF((TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row))

=IF([Actual Finish]@row <> "", 1, (TODAY() - [Actual Start Date]@row) / ([Projected Finish Date]@row - [Projected Start Date]@row))

It is shorter, but I had already typed up the portion to calculate the actual % complete. I saved keystrokes by simply copy/pasting that portion into the IF. So manually typing it all out, the [Actual Finish] saves keystrokes, but because I built the formula out of order, it was easier for me to just copy/paste instead.

Linda F

@Paul Newcome You are awesome !!! Thank you so much, I have learned something new !

Paul Newcome

Happy to help. 👍️

Linda F

@Paul Newcome I have a follow up question for calculating an elapsed time %. Below is the new schedule I am trying to set up. Similar to previous question. On this schedule, I may not always have dates or some status' may not be applicable to XX projects. What is my formula if I don't have dates for my formula to run? Below is what you helped me with last month. Because there are no dates, the error message is #DIVID BY ZERO or it is going beyond the 100 % point. Can you help me set this one up?

=IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))

Paul Newcome

To cap it at 100%, we use an IF statement to say that if it is greater than 1 (100%) then output 1, otherwise output the calculation:

=IF(IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row)) > 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row)))

To take care of the error, we use an IFERROR to output whatever you want (I'll use blank for this example):

=IFERROR(IF(IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row)) > 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))), "")

Linda F

@Paul Newcome So, this is a snapshot of the results of the IFERROR formula. I don't think it's correct. -125% should be 100% correct?

This snapshot is to to cap it at 100%. It gives me invalid operation.

Paul Newcome

Actually I just realized that the base formula we started with should have already been capping it at 100%.

=IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))

The problem is that you are entering an Actual Start Date in the future. That's where the negative numbers are coming from. Let's try this then...

=IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) <= 0, 0, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row)))

And then the IFERROR:

=IFERROR(IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) <= 0, 0, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))), "")

Linda F

@Paul Newcome It worked but I had to make a small change so it reflect 100 % and not 0 % <= 1, 1, . Thank you so much.

=IFERROR(IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) >= 1, 1, IF((TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row) <= 1, 1, (TODAY() - [Actual Start Date]@row) / ([Estimated End Date]@row - [Estimated Start Date]@row))), "")

Paul Newcome

That portion was supposed to take care of the negatives which is why we used the "less than zero". The portion before that was if it is greater than 1 to output 1. Then the final portion is that if it is greater than zero and less than 1, output the actual value.