# Calculating elapsed time in percentage

Options
✭✭✭✭✭

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

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
edited 04/07/21
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
edited 05/18/21
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

@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))), "")

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!