SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.
Sign up to join this community
I've got two columns 'Contract Commencement Date' and 'Contract End Date' and I'm trying to show the time remaining as a percentage in a third column.
Using Flow I've got a field to show todays date, and I'm using some JSON to colour code the field but I'm not getting the correct percentage. I've set the Percentage Test column up as calculated field with the following formula: =TodayDate-[Contract Commencement Date] as a percentage. Where am I going wrong
Assuming your TodayDate column is updated automatically every day (which should then also re-trigger your calculated field), then your formula should correctly calculate the total number of days that have elapsed since the Start of the Contract. I will point that this means if the [Contract Commencement Date] is in the future, then your formula would return a negative number. You can avoid that by changing the calculation to MIN(0, TodayDate-[Contract Commencement Date]). However, this still only gives you total elapsed days, not a percentage.
If you want a percentage, you want to divide the total days elapsed by the total number of days in the contract The total days in the contact would be ([Contract End Date]-[Contract Commencement Date]), so your total formula would be:
=MIN(0, TodayDate-[Contract Commencement Date])/([Contract End Date]-[Contract Commencement Date])
ROUNDUP or ROUNDDOWN where the first parameter is the value to round and the second parameter is the number of digits after the decimal point. So, to round up to whole number =ROUNDUP(TodayDate-[Contract Commencement Date]/([Contract End Date]-[Contract Commencement Date]), 0)