enter image description here 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' 
      & "    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.

  • Edit your question and add spaces to your code or use the code button in the edit-toolbar to format your code.. its a mess now. Make sure each &" starts on a new line so its readable Commented Jun 23, 2016 at 18:19
  • I have added the code and also used a picture. thanks a lot for quick reply. Commented Jun 23, 2016 at 19:01
  • The code you added at the bottom is not the same as the image. To format code on this site, copy all of it in as text, highlight it and select the {} icon. It will format it as code for us to view, similar to this: =IF(ISBLANK([Target Completion Date]),"Missing date",IF(OR([Status]="1. Open",[Status]="2. In Progress"),"", "")))
    – Erin L
    Commented Jun 23, 2016 at 19:38

1 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

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.