Calculate Column Based On Self (Circular Reference Workaround)

Question

I have a date and time data type calculated column named "Date of Review" with the following formula:

=IF([Status]<>"Select",NOW(),"")

Formula explained, if the choice column "Status" has a value other than "Select", the "Date of Review" column will be populated with NOW(), in effect creating a timestamp for when the status was changed.

The problem is any time the record is edited, the "Date of Review" refreshes to NOW().

I understand that you can't use ISBLANK() but I understand I could compare the current value of the column to another.

With that, I created a column "EmptyDate" that is not on any form and is always blank.

What I am trying to do is something like this, but I don't know what I am missing:

=IF([Date Of Review]=[Empty Date],(IF([Status]<>"Select",NOW(),""),"")

I can't get it to work. Any suggestions?

Thanks!

Answer 1

For recording the time when the status change, I will suggest you to use a flow. You cannot do a self reference in calculated column, but you can add an if condition in flow to check the Date of Review column. Only update the field value when it is empty. Remember to input the null value in the expression like this.

Answer 2

As you have mentioned in tags, you are using SharePoint Enterprise, so in such a case, you can't use Microsoft Automate (Flow) in a SharePoint On-Premise environment that doesn't have an on-premises data gateway installed.

So in this case, Instead of using a calculated column, you have to use SharePoint Designer Workflow to do the following:

• Create a workflow that runs on item add or update
• Add If condition to check if the date field is empty or not and the status column value is "Select"
• If the above condition is met, so add the update the list item action to update the date value with today.

Check also

• update the list item if the date value is null!
• Supported and Unsupported Columns In SharePoint Calculated Column Formula
• Install Workflow Manager for SharePoint 2016/2019