Tracking specific date when status changes automatically in SharePoint

Question

I hope we are keeping safe..Please i need help on a specific formula to use.The thing is i have a share point list that tracks support rendered to customers and i want to auto populate the dates of each status .I have a column called (Status) with options(Approved, initiated, completed).I want share point list to automatically input the date each status changes.I have created calculated fields with the below formula below:

** Auto Initiated date** (IF([STATUS]="Initiated",TEXT(NOW(),"dd/mm/yyyy hh:mm"))

Auto complete date (IF([STATUS]="completed",TEXT(NOW(),"dd/mm/yyyy hh:mm"))

Auto Approved date (IF([STATUS]="approved",TEXT(NOW(),"dd/mm/yyyy hh:mm"))

The issue is that the moment users change the status from Initiated to completed, The auto date disappears in Initiated column and appears in completed column.

I want the date to be retained for each column regardless of whether the status changes or not.

I have attached whaat the display looks like on my list as picture.

Answer 1

I don't think that this is possible with calculated columns. The columns will recalculate each time you change the item. Since you haven't provided an "else" option in your IF Statement the formula makes the column null when the Status doesn't match.

You can accomplish this using a workflow or Flow which runs on create and update and checks the Status and sets the date/time of the appropriate column.

Set up a flow in Power Automate as follows:

Pre-Setup:

1. Add a Column to your list called "Prior Status" or something similar.
2. Add Standard Date columns for each status you are tracking

In Power Automate:

1. Create an Automatic Flow using the SharePoint trigger "When an item is created or modified" and set your site and list name.
2. Click the ... on the right top of the trigger and select "Settings" add a trigger condition similar to "@not(equals(triggerBody()?['Custom_x0020_Status']?['Value'],triggerBody()?['Prior_x0020_Status']))" - NOTE: This is to prevent the Flow running when the status has not changed but other fields have. Expressions are fussy, so skip until you verify the flow works correctly.
3. Add a "Switch" action. For each case enter your different status values.
4. Add an "Update Item" action under each case. Update the appropriate date field by inserting the expression utcNow(). Be sure to also update your "Prior Status" column to match the selected status value.

Picture of my test flow is below: