Read calculated column previous value if status equal Closed

Question

I have a "Days Past Due" calculated Column and used your calculated formula (link Sharepoint 2013 Custom List Calculate difference between Today and Column Value) and is working fine. But there is another column called [Status] and if value equals "Closed" then it should bring the previous days value. Meaning will freeze the value and will not get changed next day.

Example: Days Past due-> "7 days Past" and Status-> "In Progress" then tomorrow it should increase by one. But if I edit the item tomorrow and set Status-> "Closed" then it should show previous calculated column data which is "7 days Past"

Below is the code I tried but did not get result.

=IF(ISBLANK([Target Completion Date]),"Missing date",
    IF(OR(Status="1. Open",Status="2. In Progress"),
    "<img src='/_layouts/images/blank.gif' 
    onload=""{"
      & "    var SPday=new Date(); "
      & " SPday.setFullYear("
      & YEAR([Target Completion Date])
      & ","
      & MONTH([Target Completion Date])-1
      & ","&DAY([Target Completion Date])
      & ");"
      & " var Days=Math.round((SPday.getTime()-new Date().getTime())/86400000);"
      & "    this.parentNode.innerHTML=Math.abs(Days)+' days '+((Days<0)?'past':'left');"
      & "}"">","<img src='/_layouts/images/blank.gif' onload=""{ "
      & "    var Days=this.parentNode.text();"
      & "    this.parentNode.innerHTML=Days;"
      & "}"">"
    )
)

Any help will be appreciated.

Thanks a lot.

Answer 1

Note: the above code only displays something in the View, it does not save any value

if I set Status-> "Closed" it should show previous calculated column data which is "7 days Past"

That is not possible this way, as Calculacted Columns always calculates

You need an (on change) Workflow solution that saves the date when Item was Closed to a separate field