There’s a well known SharePoint trick that allows you to use [Today] (the current date) in calculated columns. You’ll find several posts that explain it, here is for example a recent one:
http://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/
There are some limitations to this trick:
-You have to recreate the dummy [Today] column every time you need to edit the calculated column(s).
-The calculation is “static”. Do not expect the value to automatically refresh everyday, it will only be updated the next time you edit the list item.
So you should actually interpret “Today” as the date when the item was last modified.
But wait… isn’t there a column that already provides this value? Why not just use the [Modified] column? Let’s compare these two values on a simple example, using the tasks list from my previous post.
The formulas below calculate the number of days left until the due date:
- using the “Today” trick: =[Due Date]-[Today]
- using the default [Modified] column: =[Due Date]-[Modified]
The result (today is August 14th):
There’s a slight difference, this is because [Today] provides the current date at 12 am, while [Modified] provides the current date and time.
To replicate the behavior of the [Today] column, we just need to round down the [Modified] date:
=[Due Date]-ROUNDDOWN([Modified],0)
The result:
My conclusion: don’t waste time with the “Today” trick in your calculated columns, simply use:
- Either the [Modified] column to get the date and time
- Or ROUNDDOWN([Modified],0) which behaves the same as the “Today” trick
In a future post I’ll explain my method to get today’s date…everyday!
Update [December 2008]
- I have set up a live demo for the “Today” trick.
- Workarounds to work with today’s date are available here and here.
36 comments
Comments feed for this article
August 14, 2008 at 5:19 pm
EndUserSharePoint.com: Resource for Calculated Columns : End User SharePoint
[...] Calculated columns: the (useless?) “Today” trick [...]
August 21, 2008 at 8:51 am
Tal Winter
Looks good.
But what happens when I modify this item?
Lets say that i use it for Contact list and I need to add the persons phone number?
The Modified will be changed.
No?
August 21, 2008 at 9:25 am
Christophe
Tal: that’s correct. Whether you use the [Modified] column or the “Today” trick, the behavior is exactly the same: they are updated when you edit the item.
So I am just saying that using the [Modified] column is more straightforward than using the “Today” trick, for the same result.
August 25, 2008 at 3:22 pm
JD
Your method seems to work as long as you create or make changes to the list as described. Has anyone found a simple method by which Today can be dynamically set and then used in a calucaltion when the list is displayed?
August 25, 2008 at 4:32 pm
Christophe
Joel, have you checked out this other post:
http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/
September 12, 2008 at 10:56 pm
Fernando Gonzalez
Christophe
How can I make the “Today”column to update everyday?
This has been a big issue in teh past, and I haven´t found any way of doing it.
Any ideas?
Thanks
September 12, 2008 at 11:50 pm
Christophe
Fernando, the only way to update a SharePoint column is to edit it. So to make the column update everyday you need to run a workflow or application everyday… or edit each item by hand.
To render today’s date, you can use the DVWP and modify the XSLT. I show one method that relies on JavaScript in this post:
http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/
October 22, 2008 at 7:21 pm
WW
I think you’ve got it wrong (or I do).
The ‘trick’ is that you are tricking SharePoint into using the “Today” function call. That is the reason why you delete the [Today] field.
SharePoint thinks it is calculating a value based on two fields – when it is actually making a funtion call – and comparing the result with another static field.
Since the values in my calculated fields change from day to day without modification – I think I’m right on this one.
October 22, 2008 at 11:29 pm
Christophe
WW: are you saying that you see the value of your calculated column change, without editing, or running a workflow, or any other modification?
I’ll propose what I have proposed to others before: let’s set up a list we both have access to, follow your method, and then see how the values evolve day after day without any editing.
October 30, 2008 at 2:51 am
Christophe
I just came across another post, by Jason Huh, about calculated columns being static:
http://www.jyhuh.com/blog/archive/2007/09/21/Calculated_Column_Type_Is_Static.aspx
An excerpt:
“You should not think that the records can be somehow manipulated real-time using a Calculated Column type. Instead, you should acknowledge this limitation and treat SharePoint list items and document records as static records.”
December 8, 2008 at 9:21 pm
Cory
I’m trying to get a view on a list to show me the items modified in the last 30 days – is that something that this can help me do?
December 8, 2008 at 11:13 pm
Christophe
Cory, this is exactly the kind of stuff this “Today” trick CANNOT do.
To display the items modified in the last 30 days, just apply a filter in the settings of your view (I assume you have SharePoint 2007):
[Modified] greater than [Today]-30
The above formula uses the regular Today function, not the trick.
December 17, 2008 at 8:34 pm
F.P.
Hi, I trying to display on a view the items where the ‘Completed Date’ field is two months ago. For example from today it will show all the items from 10/17/2008 until now. When I try to do this using a filter it does not accept the [Today]- 30 , i get a message Filer value is not a valid date.
It doesnt accept the -30. any help??
December 17, 2008 at 11:21 pm
Christophe
F.P., make sure you use [Today]-30 (no space in the formula). Btw I suppose you mean [Today]-60 in your case.
January 13, 2009 at 5:41 pm
Jason Riley
Hi Christophe,
Thanks for the helpful post…
I’m using the “Useless Today trick” to calculate [Course_Start_Date]-[Today]. If the result is equal to 12 days then the workflow I’ve attached to the list will send off a reminder email to the student. Obviously, the problem is that [Today] is not dynamically updated. Can I use the the “A countdown for tasks lists” method to accomplish what I’m trying to do?
Many Thanks!
-Jason
January 14, 2009 at 12:18 am
Christophe
Jason: it won’t work, as the countdown is just a rendering effect SharePoint doesn’t know about.
The usual way to address your issue is to run a daily routine that updates all your items (and you can simply use the Modified column instead of Today).
You could also create a calculated column equal to [Course_Start_Date]-12, and check with your workflow when the current date reaches it.
January 15, 2009 at 3:52 pm
CJ
So, not to beat a dead horse, is anyone aware of possible upcoming changes in SharePoint that will allow this type of dynamic calculation to occur?
January 29, 2009 at 4:19 pm
Kurt Hudson
I use the [Today] trick every day to maintain a work item list of hundreds of entries. As long as you create a new dummy [Today] column each day, then delete it – all your entries will update. What I am continually looking for is a way to automate that. I need a way to do it that doesn’t involve having administrative access to the server. What doesn’t make sense to me is that all the solutions I have seen involve dropping code on the server. I don’t know why I cannot drop code on the client. I mean, I have the rights to create/delete the today column, so I would think it’d just be a matter of getting the commands in a sort of batch or script on my client.
January 30, 2009 at 7:55 am
Christophe
You know what, Kurt? A lot has been written about the “Today” trick, but AFAIK you are the first one to mention deleting/recreating “Today” as a way to update all entries. All the posts I read just mentioned the delete/recreate operation as a necessity when you have to update the formula. A big difference!
Still, the issue remains that such an operation can only be done by a site admin, not regular contributors.
To get to your point: have you considered Web Services? They seem to be the natural path to answer your request.
February 6, 2009 at 4:56 pm
V
I am having a problem with your method. I am trying to make an Expiry Date in MOSS which I have as =[Modified]+730 in the Calculated Column. This works fine until I upload a document and don’t immediately open it.
If I don’t open it, the Expiry Date shows as 30/12/1901, even though the Modified Date is indeed today’s date.
Is there a way to get it to change the Expiry Date without having to open every single document we upload?
February 8, 2009 at 11:25 am
Anonymous
Please let me know how to use [Today] in calculation in sharepoint list.
I used like below
1. Create a field called ‘Today’
2. Use it in the calculation field
3. delete the ‘Today’ field
It works fine but the calculation is not automatically getting refreshed, when I see the data in the next days and it shows wrong data.
for e.g.,
current_month = concatenate(text(today,”mmmm”),”-”,text(today,”yyyy”))
output of which is February-2009. But when I see the data tomorrow it shows as December-1899.
I am not sure why it is happening.
To correct this, I need to go to the list settings, click the calculation field and just click ‘ok’ in the edit column page. then It shows correct value.
Any idea?
February 9, 2009 at 1:41 am
Christophe
This is the expected behavior, as explained in the post.
February 9, 2009 at 3:45 am
Christophe
@V: thanks for reporting this issue. Could you confirm if you are using MOSS or wss?
February 9, 2009 at 1:06 pm
V
I am using MOSS. The problem, I think, is SharePoint gets rid of the square brackets around Modified. When I input =[Modified] everything updates but say the next day when I check the formula all that will be there is =Modified, which obviously isn’t right. What I have been doing currently is once a day going in and re-adding the square brackets but this isn’t the best solution. Do you have any suggestions?
February 9, 2009 at 3:18 pm
Christophe
I don’t think square brackets are an issue. My assumption: it is the fact that you edit the formula that temporarily corrects your column.
In wss I see the same behavior you reported. In MOSS I can’t even use the [Modified] column in calculated columns… that’s why I was asking.
Anybody else has comments on this one?
February 20, 2009 at 10:08 am
Christophe
@ Kurt Hudson: I have investigated Web Services to answer your question (as I suggested in my initial reply). If you are ready for a couple tests, you can contact me at Christophe@PathToSharePoint.com
March 4, 2009 at 4:21 pm
Hans
Great tip with the [Modified] example.
I have a problem though. I wan’t to highlight the due date in bold and red when it has been passed but I can’t get it to work.
Any tip would be highly appreciated.
March 5, 2009 at 2:12 am
Christophe
Hans, formatting a field depending on the current date is not easy to do with SharePoint. For some ideas, search for “countdown” in my blog. You can start with this post:
http://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
March 16, 2009 at 8:00 pm
MasterPage/PageLayout format date field « PANVEGA’s Blog
[...] http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick [...]
April 13, 2009 at 2:21 pm
Ron
I am using two columns in a task list to update the status of a project because i have two separate actions to track. Both are choice columns and when one is updated I want to show the date that it was updated. I’ve tried both the Modified and Today functions in a calculated column but when one column is updated it updates the date in both columns. I appreciate any advice.
April 14, 2009 at 12:07 am
Christophe
Ron: right, the modified date is attached to the item, not to specific columns.
You’ll need to create specific date columns to track the modified dates, and update them manually or through a workflow. Or depending on your situation it may be easier to create two different items to track the actions separately.
April 23, 2009 at 8:58 am
Useless calculated column today trick – XSL today() to the rescue. « Sharepoint applied
[...] the value of [Today] dynamically (see The truth about using [Today] in calculated columns and Calculated columns – the useless [Today] trick for more [...]
June 17, 2009 at 1:05 pm
Nitin
Hello All,
I am a Lotus Domino developer and presently working on Domino to SharePoint updating. From SharePoint I have to pass PageURL, SharePoint Document ID and Version to Domino into URL, to get this I have added a calculated column into view and written this code “=CONCATENATE(ID,”~”,Version)”. This code works fine when if I don’t CheckOut and CheckIn SharePoint document but when I CheckOut and then CheckIn the SharePoint document it gives me blank values.
Please guide me how can I get this every time.
June 19, 2009 at 12:33 pm
Nitin
Dear All,
I found the solution, I written my own javascript functions to get the answer of my last post on this site.
I tried to add my code but it said “discarded”, let me know if anyone want that code or is there any other site where I can post my code.
Thanks
June 19, 2009 at 12:45 pm
Christophe
Nitin: feel free to send me your code by e-mail:
Christophe@PathToSharePoint.com
I’ll review it and publish it as a guest post on my blog.
If you are interested, here are the instructions to post code:
http://support.wordpress.com/code/
June 24, 2009 at 11:50 am
Christophe
I am posting the script Nitin sent me. It scans the Web page to identify the “id” column, then retrieves the id for each item.
//++++++++++++++++++++++++++++++++++++++++++++++++ function getColumns() { var theTHs = document.getElementsByTagName("th"); var arr=new Array(); for(var i=0; i<theTHs.length; i++) { var THContent = theTHs[i].innerText || theTHs[i].textContent; arr[THContent] = i; }//for return arr; } var theTable = document.getElementById("onetidDoclibViewTbl0"); //The "onetidDoclibViewTbl0" is table Id I got from view source. var arr = getColumns(); function getRowIndex(id) { var colIndex = arr["ID"]; //returns e.g. 10 //0 = header row hence skipped. start with first row //find the row corresponding to the parameter "id" for(var i=1; i<theTable.rows.length; i++) { var txt = theTable.rows[i].cells[colIndex].innerText; if(txt == id) return i; //current row index } //for return -1; //parameter id not found in ID column } //function var rowIndex = getRowIndex(currentItemID);