39

I want to use a combination of Today and Me in SharePoint 2013. I am trying it in Office 365 it gives me the below error

Calculated columns cannot contain volatile functions like Today and Me.

Can anyone let me know what am I doing wrong?

Formula I am using:

=Me&" - "&TEXT(Today,"dd-mm-yyyy")
17

You cannot directly specify today and me in calculated column to set the values. You will have to create columns named Today and Me and then use it in your calculated column.

Once you are done with the formula for the calculated column, you need to delete the 2 columns created for this. I think this will make your things work.

  • 5
    While this is a possible solution, my suggestion is not to use it because it is not officially supported by Mcirosoft. – Juan Pablo Pussacq Laborde Aug 3 '15 at 12:08
  • +1.Even if it's a workaround. – Taran Goel Aug 4 '15 at 4:25
  • 5
    -1 stop using this [Today] trick; It does not work. As Christophe allready documented back in 2008. blog.pathtosharepoint.com/2008/08/14/… – Danny '365CSI' Engelman Aug 4 '15 at 17:53
  • 1
    @Danny Point taken. But I think it doesn't have any cons as such or atleast I am unaware of those. So, taking it just as a work around is still fine. – Amit Aug 7 '15 at 9:27
  • 4
    use Today() function instead of the [Today] create/delete column trick..saves some typing. If you reload the View page long till past midnight .. 'Today' will be yesterday – Danny '365CSI' Engelman Aug 7 '15 at 12:57
58

Update 6/2017: Do read: June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Calculated Column documentation: http://viewmaster365.com/365coach/#/Calculated_Column_Functions_List



Me in a Calculated Column

You can not use [Me] in a Calculated Column Formula.

You can use [Me] in de View Filter settings

Like the [Created By] and [Modified By] Columns and Lookup Columns [Me] is not available for Calculated Columns as the textual information is not stored in the List Item.
I presume its legacy behaviour. Although with todays processing power those extra Lookups might not hurt server performance, 15 years ago it really did.

get [Me] with Javascript

More at: How to get current user with javascript?

Today in a Calculated Column

It was documented long ago by Christophe that the TODAY trick by adding and deleting a Column named TODAY does not work:
http://blog.pathtosharepoint.com/2008/08/14/calculated-columns-the-useless-today-trick/

  • Calculated Formulas are only modified/updated when an Item Changes

Read that one more time:

  • Calculated Formulas are only modified/updated when an Item Changes

Got it?

That means that any reference to a Today function will
get you the result from the LAST item update.
Today does not behave the same as in Excel.

Why you may think it works

When you change a Formula you are essentially updating all List Items; so yes, the Today result is correct... but wait till tomorrow and it will be 1 day offset.

If you do want this behaviour (but WHY? would you, as TODAY() is the same as [Modified]) and you want to type the 5 characters TODAY, you can use the Today() function in a Formula. No need to create and delete columns. Note that the Now() function gives you a timestamp (like the default Modified column

Filter View by Date

You can only use [Today] in the View Filter options
(add or substract the number of days, 5475 = 15 years)

Important: Do NOT use spaces in these Formulas!!!

For more advanced use of [Today] Filtering (Filter by current Month etc.) see: http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/

But Filtering does not get you Today calculations in a View ...

Displaying a [Due Date] calculation in a View based on todays date

Your browser knows todays date, so you can use Client Side Rendering (CSR) and some JSlinks to change how Views are displayed.

Now that requires some JavaScript programming.

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Original answer:

If you are lazy, like I am, you can abuse a Calculated Column to display HTML/JavaScript

  • Create a Calculated Column
  • Set the datatype to Number
  • Paste the Formula

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

Notes:

  • Full explanation on Why this works (and When not to use it) at: http://www.viewmaster365.com/#/How especially read the pros & cons!!
  • JavaScripts Months start with 0 for january, so you have to substract 1
  • the trick image to fire the Javascript is replaced with your text
  • Unlike CSR there is no need to include the Due Date in the View
  • All this only works in Views, for Forms you have to use CSR

Adding some color

Only 5 lines of javascript extra (try that with CSR), and the table rows are colored based on a Range and matching CSS colors

    =IF(ISBLANK([Due Date]),"Missing Due date",
    "<img src='/_layouts/images/blank.gif' onload=""{"
    &"    var SPday=new Date();"
    &"    SPday.setFullYear("
    &YEAR([Due Date])
    &","
    &MONTH([Due Date])-1
    &","
    &DAY([Due Date])
    &");"
    &"    var Days=Math.round((SPday.getTime()-new Date().getTime())/86400000);"
    &"    var Range=[ -365*20 , -365*10 , -365*5 , 0 , 7 , 14 , 21 ];"
    &"    var CSS=['GoldenRod','lightCoral','Pink','#FAFAD2','lightGreen','mediumSeaGreen','limeGreen'];"
    &"    for (var i=0;i<Range.length;i++){var Color=CSS[i];if(Days<Range[i]){break}}"
    &"    var TR=this;while(TR.tagName!='TR'){TR=TR.parentNode}"
    &"    TR.style.backgroundColor=Color;"
    &"    this.parentNode.innerHTML=Math.abs(Days)+' days '+((Days<0)?'past':'left');"
    &"}"">")

A compacted version

Someone (rightly) complained this script is added for every List Item. In a View with a 1000 items that adds some weight. So the Javascript needs to be as compact as possible.

She also wanted to dim the Rows that were completed.

    =IF(ISBLANK([Due Date]),"Missing Due date","<img src=/_layouts/images/blank.gif onload=""{" 
    &"var T=this,C=0,N=~~((new Date("   
    &YEAR([Due Date]) & "," & MONTH([Due Date])-1 & "," & DAY([Due Date])   
    &")-new Date())/864e5);"    
    &"while(N>[-365*20,-365*10,-365*5,0,10][C])C++;"    
    &"while(T.tagName!='TR')T=T.parentNode;"    
    &"T.style.backgroundColor='#'+['DA0','FF0','FCC','9E9','3B7','3C3'][C];"    
    &IF([Completed] , "T.style.opacity=.7;" )   
    &"this.parentNode.innerHTML=N<0?-N+' days past':N+' days to go';"   
    &"}"">") 

This adds 340 bytes per Item (But NOT for Blank Dates!) to the page, that is 332 Kb for 1000 items, roughly 10% of the whole List View page.

If page size is an issue then offload this to a CSR function

Note the use of [Completed]
The Formula is pre-building the Javascript; in CSR this would be a condition which is evaluated over and over again... also note that CSR can only operate with data in the View. In CSR you can't color the items red that were never changed if those fields are not displayed in the View, here it is one line:

&IF( [Created]=[Modified] , "this.parentNode.style.color='red';")

CalcMaster Bookmarklet to edit Formulas

It is a PITA to debug Calculated Columns. Because you don't get feedback until you save a Formula and you end up having to click multiple times to get back to your Formula.

I have written a small 'CalcMaster' bookmarklet which hooks into the formula-editor and does a save of the Formula on every keypress; giving immediate feedback.
Published a first version on GitHub:

https://github.com/Danny-Engelman/CalcMaster

Writing less CSR JavaScript with iCSR.js

This iCSR Open-Source CSR support library can do Today calculations and make formatting easy.

If you have a column "ContractEndDate" (or any DateTime column), you declare:

Change the View definition to:

      "ContractEndDate":{//date column with the contract end date
        View : iCSR.DueDate({
          ranges:     "coral/red,-1,lightcoral,0,khaki/red,89,lightgreen,90,yellowgreen",
          colortag:   "TR",
          red:        "style=color:[msRed]",
          labelNodate:"<i [red]>No Contract Enddate!</i>",
          labelToday: "<b [red]>Today</b>",
        })

No need for OnPostRender or anything else

And you get:

iCSR.js is open source on GitHub

Editing CSR files

Please use the Cisar Chrome Browser extension by Andrei Markeev

iCSRjs

I1 ICC top20

  • 1
    This is an extremely helpful post for anyone who doesn't fully understand the power of calculated columns. It deserves a lot of upvotes. – psdpainter Aug 4 '15 at 19:29
  • 1
    You might like viewmaster365.com/365coach/#/Calculated_Column_Functions_List as well, then – Danny '365CSI' Engelman Aug 4 '15 at 19:35
  • Your answer is quite insightful though it doesn't help resolving my problem. I need today and me for logging purpose and not for displays. Though many will be helped by this. +1 – Taran Goel Aug 6 '15 at 19:07
  • 1
    A problem with using calculated columns like these is that they cause an error when you query using listdata.svc 2010 rest api. – Akhoy Feb 25 '16 at 2:12
  • 1
    Yes, because the full code is returned which breaks the XML, that is why Microsoft wraps the whole Formula in CDATA tags when you use the SCRIPT tag. But with current capabilities using WebServices is like buying a horse and cart instead of a Tesla. REST returns these fields just fine. – Danny '365CSI' Engelman Feb 25 '16 at 8:55
15

The built in [Today] and [Me] values have never worked in calculated columns, all references to this in previous versions were "hacks" as Sane mentions.

The best ways to do this would be either via workflow on item creation/edit you update a fields based on the formula you desire or you could try a remote event receiver to handle it.

  • where is the value of [Today] configured? Is it the regional settings we have in site administration or the settings we have in web application in central admin? – Vignesh Subramanian Jan 20 '16 at 9:00
2

In 2018-5-5, I creat a column duedays=critical-Today() it works It means SharePoint improve this bug anymore

  • The question is about using Me and Today, your answer looks incomplete. – Asad Refai May 6 '18 at 5:15
  • SharePoint was block use today() in calculator column before, so programmer should write another column to remember current today and use workflow to update it, it was sad thing before, now use today() is possible in SP online, another change documents library no long "document20%library"... – user3626945 May 10 '18 at 1:03
2

@User3626945 is correct. Even though you can not use the [TODAY] value, you can calculate using the TODAY() function.

For example, to find the number of days remaining on a contract; create a calculated value column called "Days Remaining", and set the formulae,

=[Expiration Date]-Today()
  • I must add, I don't know if this is something that has changed, but it is definitely working now. – user77481 Jul 24 '18 at 11:29
0

I would use SharePoint Designer to create a workflow that writes to a plain text field. You can have it trigger on creation only and write the user and the date/time. I use it to write custom logs. Google for examples.

0

If someone is using SharePoint online and you don't want to use undocumented feature in calculated columns (i.e [Today]). There are two options for you. 1 >> Instead of have calculated column in the list. You can have calculated column in view using JsLink 2 >> Use Now() methode instead of Today AND Use scheduled Powershell script to update field formula every morning. this will refresh your values in column every morning. all you need to do is to update formula to something else and change it back to original. this approach is only useful if you are handling date (not time)

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.