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

Find more posts tagged with

Calendar View

Formulas

Comments

Mark Mader

Hi Thor -

Thanks for posting. Our support and QA teams will investigate.

Best Regards,

Mark

Travis

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.

Thor

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