1

Currently working on documents with status and expiration date and created 2 columns in SharePoint I want the status column to automatically tell me if the document is current or expired. The formula I have so far is =IF([Today]>[Expiration Date],"Expired", "Current") but I am getting an error "Calculated columns cannot contain volatile functions like Today and Me"

Anyway to fix this? response is very much appreciated.

1

Here is a formula you can use in the calculated field (Status):

Set the Data Type of the column to Number and add this formula:

=IF(ISBLANK([Expiration Date]),"No Expiration",
"<img src='/_layouts/images/blank.gif' onload=""{"
&"var date=new Date();"
&"date.setFullYear("
&YEAR([Expiration Date])&","
&MONTH([Expiration Date])-1&","
&DAY([Expiration Date])&");"
&"var DaysDiff=Math.round((date.getTime()-new Date().getTime())/86400000);"
&"this.parentNode.innerHTML=((DaysDiff<=0)?'Expired':'Current');"
&"}"">")

UPDATE

CSR to fix the HTML output on DispForm.aspx:

SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {
  function getBaseHtml(ctx) {
    return SPClientTemplates["_defaultTemplates"].Fields.default.all.all[ctx.CurrentFieldSchema.FieldType][ctx.BaseViewID](ctx);
  }
  function init() {
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
      Templates: {
           Fields: {
               "Status": {
                   DisplayForm: function(ctx) { return ctx.CurrentItem["Status"]; },
               },
           },
      },
      ListTemplateType: 101
    });
  }
  RegisterModuleInit(SPClientTemplates.Utility.ReplaceUrlTokens("~siteCollection/Style Library/HTML2TextCSR.js"), init);
  init();
});

  • Daniel, It works just what i wanted.. However when viewing the item in the form view the status column is showing the formula and not the " expired" or "current" wording . Anything i need to change? – josh0716 Jul 22 '16 at 2:55
  • Please see my updated answer. You can fix that with the CSR script. – Daniel Stölzner Jul 22 '16 at 3:25
  • do i need sharepoint designer to enter these script? – josh0716 Jul 22 '16 at 23:34
  • No need for SPD. Create a new file called 'HTML2TextCSR.js'. Copy the CSR code into it. Then upload the file to '~siteCollection/Style Library/' (don't forget to check-in/publish [if required]). Go to your DispForm.aspx, edit the page, edit the form web part, put '~siteCollection/Style Library/HTML2TextCSR.js' into the JS Link property. Save the page and then check it out, the HTML should be gone. – Daniel Stölzner Jul 23 '16 at 0:51
  • You could also use the Cisar Extension for Chrome to deploy the CSR – Daniel Stölzner Jul 23 '16 at 0:52
0

It is not allowed but there is tricky workaround for this.

  1. Open up the List Settings page where you want this column.
  2. Create column named “Today”.
  3. Now create the calculated column where you need to use current date within formula and add “Today” from available columns
  4. Once this is done, we no longer need that our own generated column “Today” so you can delete it (Although when you need to edit formula you will need this again, so either recreate this dummy Today field or make it hidden using code behind. Otherwise SharePoint will put you again on that error screen).

Read Complete Post here: Use [Today] and [Me] in Calculated Column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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