2

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

Screen Grab of the lists

0

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])
12
  • Thanks @willman. I'm getting there. I put the formula in and I'm getting 0% back in the percentage test field?
    – Dazza
    Mar 11 '20 at 17:09
  • I'm really close! I took out the =MIN(0, and put the Number if decimal places to 0 and now the percentages are coming out as 22.54853342203838% and 72..98878342203838% how do I round it up to just the whole number?
    – Dazza
    Mar 11 '20 at 17:21
  • 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)
    – willman
    Mar 11 '20 at 18:13
  • Thanks @willman. I popped that it and now all the percentages have come out as wither 4384400% or 4378900% ?
    – Dazza
    Mar 12 '20 at 13:16
  • Found this link: support.office.com/en-us/article/… There's 13 numbers to the right of the decimal point.
    – Dazza
    Mar 12 '20 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.