How to get the change date for a specific column based on its value

Question

I'm new to SharePoint and I'm not sure if the tool can help me with this issue:

I have a list with several items to organize activities/projects, as illustrated in the figure. The "Status" column is a "Choice" type, where there are some options to describe the status of each activity.

I would like that, when the person selected the option "Iniciado" ("Started", in English), the date was automatically added in the column "Data de início" ("Start date") and the same when selecting the option "Concluído" ("Done"), the date was added in the column "Data de conclusão" ("End Date").

The formula I'm using is:

In the "Start date" column:

IF([Status]="Started";TODAY();"")

In the "End Date" column:

IF([Status]="Done";TODAY();"")

You may have already realized what the problem is with this:

Looking at the third item, we see that it is "Done", but we don't have start date, because the status isn't "Started", although for it to be completed, it was started sometime.

In the fourth item, it should contain a date of beginning, since, if it is being finalized ("Finalizando", in portuguese), it is because it was also initiated someday.

I don't know if the problem was well reported, but the fact is that I want to have the start and end date of activities based on the day the status changes to "started" and "done".

Somehow I need to "lock" editing the "cell" when the date is added. I know that the formula I wrote really doesn't meet my need the way it currently is.

Is it possible to do what I want?

If you can help me, I would be very grateful!

*I'll add that the fact that I'm using the formula 'today' inside the 'if' function isn't bothering me. The date doesn't change daily like I thought it would. So if I change the status to "started" today, it will return today's date (November 8th) and tomorrow it will still be November 8th. The problem is if I change the 'status'.

Answer 1

Calculated columns are going to recalculate every time the item is updated (they aren't fixed values). When an item changes, whatever value is in the calculated field will be reevaluated. This means using calculated columns for historical tracking doesn't work (at least not the way you want it to).

You'll need to set the values of the columns explicitly (rather using a calculated column). You can do this by firing an event when a list item changes. In SharePoint Online, that really only happens through either webhooks or Power Automate. Webhooks require a technical infrastructure that is almost certainly overkill for this situation. Power Automate (Flow) is the proper way to handle this.

You'll need to have a flow trigger on creation/update of list items. If the only thing you are changing (unlikely) is the status column then you can just use that value to determine which column to update. More likely, however, you are only wanting to update the item when the status changes from one value to a new one (doing nothing if it's only some other field that was updated). In this case, you'll have to track the previous value of the column in order to know if it changed. You can do this with an additional column you exclude from the forms and set using the flow.

Answer 2

You can achieve your needs by creating Microsoft Flow (Power Automate). You can do this by triggering this Flow when the list item changes.

You can go to the Microsoft Power Automate Community to get more ​professional help