Calculating elapsed time in percentage
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
Best Answers
-
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))
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!
-
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.
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!
-
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))), "")
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
-
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))
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!
-
@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?
-
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.
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!
-
@Paul Newcome You are awesome !!! Thank you so much, I have learned something new !
-
Happy to help. 👍️
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!
-
@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))
-
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))), "")
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!
-
@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.
-
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))), "")
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!
-
@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))), "")
-
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.
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.9K Get Help
- 378 Global Discussions
- 208 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
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!