1

We have a change control SP list and do aging metrics on how long an item is open. I have a calculated field to calculate how long an item has been open, but am getting a negative serial date as a response.

Calculation: =IF(OR(Change_Control_Status="Closed",Change_Control_Status="Withdrawn"),[Closed_Date]-[Created],Now()-[Created]

When the status is Closed or Withdrawn, the calculation works fine, but when the status is Open, SP is not calculating the time between created date and the current date - it is returning values like -43,873 which appears to be a date serial number. I have also tried changing Now() to Today() and doing DATEDIF.

Improve this question

1 Answer 1

Reset to default
0

Calculated columns are only evaluated when an item is created or modified. So, if you're looking to calculate things with a fixed datetime, then you can change out Now() with [Modified].

If, however, you are looking for a column that always renders the calculation based on the current time, you'll need to use List Formatting. You can apply a column format to do this in SharePoint Online or SharePoint 2019.

This sample format could easily be adjusted to use the @now placeholder token to perform the calculation: https://github.com/pnp/sp-dev-list-formatting/tree/master/column-samples/date-difference

The sample above uses Excel Style expressions but could be rewritten with AST for 2019.

Improve this answer
1
  • 1
    Thanks for the response. I will look into using [Modified]. Unfortunately we are still using SP 2013. We are scheduled to upgrade sometime later this year.
    – bja92
    Commented May 14, 2020 at 15:50

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.