Stop counting days in SharePoint when status value is 'Complete'

Hi Nicole0117 ,

there a multiple options depending on what exactly you want to accomplish:

1) You want to have that "Days open" column count down every day automatically until the "RECIEVED" date. Just to make it visually better for your users to understand how many days are left.
"Stop counting days" means that the column should be empty, if the status is "Complete".
 
This does not work with calculated columns (as they are only refreshed on item update).
Use a column formatting JSON like this instead

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$Status]=='Complete','',floor((Number(Date(@now))-Number(@currentField))/(60*60*24*1000))"
}

 2) You want to store the exact date when the item switched to the status "Completed" for further processing.
"Stop counting days" means that the number of days remaining ist stored in the column after the status switches to "Complete". That number stays that way, not matter how many times the item is updated afterwards.

That also does not work with a calculated column (As every update on that item will trigger the formula and we have no chance to compare the current "Status" value to the previous "Status" value. )

In that case i would use a power automate flow that:

• Starts on "When an item is created or modified"
• Then uses the "Get Changed for an item or a file (properties only)" Action
• Then checks in a Condition if the column "Has Column Changed:Status" is equal to true
• And if the Column "Status" is equal to "Completed"
• In the "If Yes" Branch uses an "Update Item" Action to set the "DAYS OPEN" Column to "utcNow()"
  
  
  

Best Regards,

Sven