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

  • Whether the [Days Open] column is a date and time column? How do you want to count the [Days Open] column, subtract [Date Reported] column and [Days Open] column? Commented Aug 23, 2021 at 10:48

1 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!!! I am not sure how I would write that calculation in the [Date Reported] column. What is the best way to find that logic? Commented Aug 23, 2021 at 12:45
  • @Mike, Please check the updated answer for expression details. Commented Aug 23, 2021 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.