Need to count number of days until value of another column is set to a specific value. [Days Open] counts until [Status] = Complete

Question

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".

Answer 1

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