Feb 18 2023 04:18 PM
I have a 'DAYS OPEN' calculated column, which begins counting from the date 'RECIEVED',
=TODAY()-RECIEVED
and a 'STATUS' choice column,
New
Assigned
In Progress
Pending More Information
Complete
I am trying to set the 'DAYS OPEN' column to stop counting days when the 'STATUS' is changed to Complete. Is there a way that I can do this?
Thank you!!
Feb 19 2023 02:22 AM
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:
Best Regards,
Sven