Calculating the Percentage of Time Elapsed between two Date Fields relative to Today

Problem

In some cases, you might have a requirement to calculate the percentage of time elapsed between two fields. For example, you may want to have a Contract Start Date Field and a Contract End Date Field, along with a % of Contract Term Elapsed field which you want to reflect how far the Contract is into its duration.

There isn’t a quick and easy solution to this that will allow you to represent the amount of time that has elapsed between two date fields relative to today’s date (though there is now that you’ve found this post), at least not by using the existing date field settings currently available in Agiloft. However, this requirement can be achieved using a custom formula shown below.

Solution

The solution to this is to use a ternary formula nested within another ternary formula (most suitably in conjunction with a Calculated Result field), which is shown below:
((to_number($contract_end_date)-to_number($contract_start_date))=="0"?"":(to_number(TODAY())>to_number($contract_start_date)?(to_number(TODAY())<to_number($contract_end_date)?(to_number(today())-to_number($contract_start_date))/(to_number($contract_end_date)-to_number($contract_start_date))*100:"100.00"):"0.00"))

This may seem daunting, but the formula is actually quite simple and essentially translates to the following when deconstructed:
IF the end date and the start date are different (this check prevents divide by zero errors in the subsequent calculation), AND start date is less than today AND end date is greater than today, THEN calculate the percentage of time elapsed by dividing the amount of time between today and the start date by the contract term (end date - start date). ELSEIF today is greater than the end date, then it returns "100.00%" (End Date has passed) ELSE "0.00%" (Start Date has not yet arrived).

Neat!

I didn’t actually need this, but I did it anyway. :wink:

Works great.

Thanks!

1 Like

You’re very welcome Paul, glad to hear this is working for you and being put to good use!

Please feel free to post in our forum if you have any questions or have any other sorts of interesting design challenges you might like help with.