updatelistitems – Capture static date & time when list item “status” column changed to “6-Completed”

Using a Sharepoint List to capture issues and tasks and need to be able to determine how long each item has spent in a specific “status”.

“Status” column has choices as below;


How do i capture this information in a seperate column, that doesn’t change when any other changes are made to the list?

Have tried to set a flow when item is changed but cannot get it to work (newbie to Flow).
Have tried to use a formula to provide the Now() value in text in a seperate column – also couldn’t get it to work.

=IF(Status="6-Completed",TEXT(NOW(),"dd/mm/yyyy  hh:mm"),IF(ISBLANK(Status),"","NULL"))

I now have a list of “NULL” in every item in the Completed On column and it does not update or change when Status is changed.

Any ideas, it’s making my head hurt!

Thanks in advance – PCB