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")
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.
https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List
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/
Read that one more time:
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
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:
###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
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:
Please use the Cisar Chrome Browser extension by Andrei Markeev
iCSRjs
I1 ICC top20
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.
@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()
In 2018-5-5, I creat a column duedays=critical-Today() it works It means SharePoint improve this bug anymore
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.
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)
