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