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.

Improve this question

2 Answers 2

Reset to default
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();
});
Improve this answer
7
  • 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
    Commented Jul 22, 2016 at 2:55
  • Please see my updated answer. You can fix that with the CSR script.
    – Daniel Stölzner
    Commented Jul 22, 2016 at 3:25
  • do i need sharepoint designer to enter these script?
    – josh0716
    Commented Jul 22, 2016 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
    Commented Jul 23, 2016 at 0:51
  • You could also use the Cisar Extension for Chrome to deploy the CSR
    – Daniel Stölzner
    Commented Jul 23, 2016 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

Improve this answer
5
  • 1
    I like the way we cheat SharePoint :) :)
    – Arvi
    Commented Jul 21, 2016 at 3:25
  • :) we have sharp mind in this small world, who always looks for the workaround. i think this cheat deserve upvote.
    – Waqas Sarwar MVP ♦
    Commented Jul 21, 2016 at 3:38
  • Hi Waqas, check this sharepoint.stackexchange.com/questions/151144/… . It says we should not use this trick as this trick never works.
    – P S
    Commented Jul 21, 2016 at 4:32
  • Your are only cheating yourself, that [TODAY] column trick does not work, never worked and will never work. Explanation see: sharepoint.stackexchange.com/questions/151144/…
    – Danny '365CSI' Engelman
    Commented Jul 21, 2016 at 7:12
  • i tried but im returning a value of 12/30/1899. does it work with sharepoint 2010?
    – josh0716
    Commented Jul 22, 2016 at 2:57

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.