Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Prorate function and duration
Something seems to have happened with the prorate function in the new release, or perhaps it was always like this and I just never noticed (in which case I apologise).
I use the prorate function to provide linear projections of task completion. In other words I calculate what the completion percentage of a task should be at the end of any given day.
The formula is very simple and obviously not 100% accurate due to the previous limitation in task duration (ie whole days only), but perhaps this will now be possible if we are able to use the time component maintained in the new Date/Time columns. Anyway, it is accurate enough to be useful...
=IF([Start Date]1 <= TODAY(), PRORATE(1, [Start Date]1, [End Date]1, [Start Date]1, TODAY()), "")
The IF statement is not relevant to my actual goal, but it is included to avoid calculating for any tasks that haven't started yet.
I was reviewing the impact of some of the new functionality brought in the release and have just noticed that the prorate function seems to be working according to elapsed time duration rather the working hours duration after the release. This means the calculation is completely wrong! Obviously it has less impact the longer the duration...
Imagine the following scenario;
- Task duration - 3 days
- Task elapsed time duration - 5 days (starts Friday and finishes Tuesday)
- Today is Monday
Above formula results in projected completion as 80% (i.e. 4/5) whilst it should be 66.66% (2/3).
Any comments from the moderators? Without a fix I have to scrap my projections...
Comments
-
Hi Thor -
Thanks for posting. Our support and QA teams will investigate.
Best Regards,
Mark
-
Hi Thor - thanks for your patience while we investigate. I spoke with our QA and development teams and ran a few tests of this functionality between the current version of the app and the version prior to our release on July 11th. The PRORATE function behaves today the same as it did prior to our July 11th release. The PRORATE function does not take into account working days, which is why the result of your example is 80% (4/5) and not 67% (2/3).
It is possible to use PRORATE and get the results you are looking for by using the NETWORKDAYS function in your formula. Try this formula:
=IF([End Date]1 < TODAY(), 1, IF([Start Date]1 <= TODAY(), ROUND(NETWORKDAYS([Start Date]1, TODAY()) / NETWORKDAYS([Start Date]1:[End Date]1), 2), ""))
Let me know if you have any questions about this.
-
Hi Travis
Thanks for your help. That seems to do the trick.
I used it as written above, but nested it inside a ISBLANK IF statement to avoid saying the task should be 100% complete if the start/end dates are missing :-)
Cheers
Thor
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives