Create calculated column that increases daily and resets on value of another column

Question

Let's say I have two columns in a SharePoint list:

[Invoice Age] - text (20, 23, 25)

[Payment Status] - text (Not Paid, Paid)

I'd like to create a calculated column [Invoice Age] that will increase by 1 everyday assuming the [Payment Status] for that row is not updated to a specific value like "Paid." If the [Payment Status] for that row is updated to "Paid" then the [Invoice Age] will reset to 0 and increase by 1.

For example, a row with a [Invoice Age] of 15 will increase by 1 daily. So, tomorrow the column will reflect 16 and so on until the [Payment Status] is changed to "Paid" upon which the [Invoice Age] will go back to 0.

I don't know how complicated the above ask is but any insight/guidance will be appreciated.

Answer 1

A calculated column will not update automatically like that. The item has to be opened and saved for the column to re-calculate.

It would be better to use a workflow in this case. In the workflow you can have the condition if Payment status = paid set Invoice age to "0" Then move item to "Paid Invoices" list- else if Payment Status = Unpaid update the Invoice Age (number column) by 1.

To trigger the workflow on a daily basis you should be able to use the retention policy settings. Something like this: site settings->Information management policy settings-> Content type policies-> and select your content type for the list item. If you are not using content types this won't work so you may have to make a content type first. Check 'Enable Retention' box and 'add a retention stage' Fill time period with 'Modified Date' + 1 days, In action drop-down select 'Start a workflow' and chose your workflow which you created select the 'repeat this stage's actions' check box and set the recurrence period as 1 day.

I add the archive Paid items to a "Paid Invoices" list in the workflow, because otherwise the workflow will trigger on all the Paid items every day forever, which you don't want. It will also help keep the list item count manageable over the long term.

Answer 2

A workflow that fires on a daily routine would be the best solution in this scenario.

In addition to that, it would be a lot easier if you have a fixed value for where the [Invoice Age] should start. For example, if the payment has a base of 15, then you can set the formula as:

IF([Payment Status]="Paid","0",INT(NOW()-Created)+15)

The workflow just needs to force the item to update itself and trigger the calculation again.

See the reference here: Calculated column that updates daily.