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

I am using following formula but not sure what to use in false statement.

IF(NOT([Status]="Completed"),datedif([Date Reported],Today(),"d")

1 Answer 1


You will not be able to achieve this requirements using Today() in SharePoint calculated column formula because calculated column value is calculated/updated when:

  1. You create a new list item.
  2. You update an existing list item - any column/field value of the list item
  3. You update the calculated column formula

Hence, when you set the "Status" column value to "Complete" and any time after that, the same list item is updated, calculated column ([Days Open]) value will be recalculated based on the today's date which will cause problems for you.

Try something like this:

  1. Create a one more date column in your list (say, named as "Completion Date").
  2. When you set the Status column to Completed, update newly created date field to current date (when status column is updated) - manually or using Power automate flow/workflow.
  3. Then use calculated column formula like below:
=IF(ISBLANK([Completion Date]),DATEDIF([Date Reported],Today(),"d"),DATEDIF([Date Reported],[Completion Date],"d"))
  • Hi Ganesh, thank you for providing me the solution. Can you please help with creating the completion date workflow ? Commented Feb 6 at 17:18
  • Are you using SharePoint online? If yes, you can use the Power automate flow for your requirements. Create simple flow with created or modified trigger and one update item action to update the date value conditionally based on status. You will have to add trigger conditions based on status column value and if date completion date is empty. Commented Feb 7 at 5:23

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.