Perform column Calculation only when particular field is updated?

Question

In a custom list, I have a choice-type column named "STATUS" which has the following available choices:

• To command
• commanded
• received

I also created a Calculated column named "CHANGE" with the following formula:

= IF( OR( [STATUS]="To command", 
          [STATUS]="commanded",
          [STATUS]="received"), Modified)

When I select an option of the choice column "STATUS", the "CHANGE" column is updated with the current date. It works as I expect.

My problem is that the Calculated "CHANGE" column also updates when some other field on the item is updated, even when "STATUS" remains unchanged.
How to update the Calculated date only if column STATUS is modified?

Answer 1

Calculated columns always re-calculate when an Item is updated. That is by design. If you want a field to only update conditionally based on some rule, you will need to create a workflow.

For your specific scenario, first create a Hidden text column named "OldStatus", then change your "CHANGE" column to be a non-calculated Date column, and also make it Hidden. Note, you can still shows "hidden" columns in list views, they just don't show up on Forms.

Then create a workflow that is triggered on Item Created and Item Updated. In the workflow, check to see if the value "OldStatus" is equal to "STATUS". If they are equal, perform no further actions in your workflow. If they are not equal, then set the value of "OldStatus" equal to the current value in "STATUS" and update the "CHANGE" column to the current date.

Answer 2

You either need to use a workflow and add the condition only if the dropdown value is changed.For this you need to setup a field which would hold previous value. On edits, you need to check in the workflow new value to old value, that is how workflow condition would validate a choice change. If true, then set the date field in workflow.

If you want to go with calculated column route, I would create a new column which would save the previous value of the dropdown field, and somehow compare the previous field value to the STATUS current selected value.I am not sure if we can compare two values in calculated column(I am researching on this)

One final thing would be to use a simple javascript, to check for column change and update the date column with current time and date.