I have a [Date Reported] column and a [Status] column.
I want to count the [Days Open] value until the selection in the [Status] column is set to "Complete".
Once set to "Complete", I need the [Days Open] value to remain, not reset to "0".
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.
Sign up to join this communityI have a [Date Reported] column and a [Status] column.
I want to count the [Days Open] value until the selection in the [Status] column is set to "Complete".
Once set to "Complete", I need the [Days Open] value to remain, not reset to "0".
One solution could be,
When [Status] changes to "Completed", capture the date in a new column [DateCompleted]. In your calculated column write the logic to use days calculation from [Date Reported] till [Status] changes to Completed, and if status is "Completed" get days between [Date reported] and new column [DateCompleted].
It will look like this:
=IF(NOT(Status="Completed"),DATEDIF([Date reported],TODAY(),"d"),DATEDIF([Date reported],[DateCompleted],"d"))
You may need to handle null value checks, otherwise it results unexpected values. And also you need to add Today as a list column before creating the caliculated column.
Please use below reference for, how to add Today in calculated column How To Get The Current Date In SharePoint Calculated Column
Thanks