Sharepoint - How to use Today and Me in Calculated column
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:
https://www.365csi.nl/vm365com/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
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.
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.