0

I've got a field called Term months which contains the length of a contract in months and a field called Days Left which has the remain length of the contract in days. What would the formula be for creating a percentage of the time left based on these two fields? I'm using SharePoint Online.

Thanks in advance.

4
  • Do you have a field with the Start date? I.e. what date are we calculating months from. – willman Mar 2 '20 at 12:40
  • @willman Yes there's a field called Contract Commencement – Dazza Mar 2 '20 at 15:18
  • Are you using Modern list views in either SharePoint Online or SharePoint 2019, or are you using some other version of SharePoint? – willman Mar 2 '20 at 15:53
  • Do you already have a process that is keeping your "DaysLeft" field updated every day? – willman Mar 2 '20 at 19:11
0

So, the answer is Yes, but No.

If you have the ContractCommencement Date field, and the number of months of the contract, you can calculate the Contract's end date with this formula:

=DATE(YEAR([ContractCommencement]),MONTH([ContractCommencement])+[TermMonths],DAY([ContractCommencement]))

and yes, this formula does still work even if the the date spans across year boundaries. Per the documentation on the Date function:

Month is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,14,2) returns the serial number representing February 2, 2009.

However, in order to use your newly Calculated "Contract End Date" to determine the number of days left, you have two big hurdles to overcome. The first is that the built-in "[TODAY]" formula value to compare against the current Date is Not Supported in Calculated Column formulas. As stated in the formula documentation:

The TODAY and ME functions are not supported in calculated columns but are supported in the default value setting of a column.

If you hunt around, you will find posts on using certain tricks to make "[TODAY]" available in your formula, but in most version of SharePoint, those tricks do not work any more, and even if you could get it to work, that brings you the second problem, and that is that Calculated Fields only re-calculate and update their value when the Item is otherwise updated. So, even if you managed create a formula that calculates Number of Days Left, it would not updated automatically every day, unless the item was updated every day.

So, If you truly want a column to hold the value of the "Number of Days Remaining", then you would need to implement a workflow or a scheduled script to crawl the items every night and recalculate the value.

On the other hand, if you don't care about storing that value, and just want it displayed when a user views the list or list item, you can created your newly Calculated "Contract End Date" field and use the JSON column formatting featurein Modern List Views to change that field to dynamically display "x days remaining".

6
  • Hi William. Thanks, it's not quite what I need, I need to show a percentage of how much of the contract has elapsed. Thanks for your help – Dazza Mar 2 '20 at 16:51
  • Right, but the only way you can calculate what percentage has elapsed is by comparing the EndDate with Today's Date, which you cannot do in a Calculated column. – willman Mar 2 '20 at 17:18
  • Hi @willman I'm swinging back round to this, and I see what you mean about the [Today] issue. I've tried to create the percentage by using this formula: =([Contract Commencement Date]-[Contract End Date])/ABS([Contract Commencement Date]) – Dazza Mar 11 '20 at 12:43
  • Hi @willman I'm swinging back round to this, and I see what you mean about the [Today] issue. I've tried to create the percentage by using this formula: =([Contract Commencement Date]-[Contract End Date])/ABS([Contract Commencement Date]) and JSON in the column and for example '-1.68307472396786790%' ideally with I want it to show say '60%' . Help! Where do I go from here? Could I do it Flow? – Dazza Mar 11 '20 at 12:51
  • Hi I think I'm getting closer to a solution. Using Flow I've created a filed called TodaysDate which has - you guessed it - today's date in it. I thought then it would be a simple case of: =TodayDate-[Contract Commencement Date] the odd thing is that SharePoint keeps taking the [ ] of the TodayDate field. Am I getting closer to a solution....? Any suggestions....? – Dazza Mar 11 '20 at 15:26

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.