If you try to create column with field type : calculated and want to use current date as part of formula , the first thing that comes up in mind is to use [Today] as SharePoint gives it as filter option while creating views. But unfortunately if you try to add ‘[Today]‘ in formula, it gives error : “Calculated columns cannot contain volatile functions like Today and Me”
Same error goes when trying to use “[Me]” too.
Well, a nice trick can allow you to do this.
- Open up the List Settings page where you want this column.
-
Create column named “Today”. The type doesn’t matter here, so let type as “Single Line of Text” and just click “Ok”
-
Now create the calculated column where you need to use current date within formula and add “Today” from available columns
-
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).
This will trick the SharePoint to use [Today] (current date) as part of formula.
You can apply same trick for the current user i.e. [Me] too. Create a dummy field named “Me” use it in your formula, save the calculated column, and delete that dummy “Me” field.
For calculated fields, calculated value is stored in Database when item is created at first time and doesn’t change until item is edited. So using Today doesn’t means each time you will get value re-calculated as per actual today’s date. For that it is better to use Computed column.
This trick tells how you can use Today & Me for calculated fields. Its secondary thing whether to use them or not. This totally depends upon usage of calculated field. For example you can use this in project management site for issues list, whenever new issue item is created you need to auto calculate the minimal duration and base on production push date of solution.
In Part 2 we’ll see how to use Object Model to do this, as well as using the Computed Field to create more advance formulas
SharePoint, SharePoint and stuff : SharePoint Kaffeetasse #68 said,
May 22, 2008 at 1:00 PM
[...] Use [Today] and [Me] in Calculated Column [...]
Mano said,
June 5, 2008 at 7:00 PM
Great .. thanks
Calculated columns: the (useless?) “Today” trick « Path to SharePoint said,
August 14, 2008 at 2:20 PM
[...] Calculated columns: the (useless?) “Today” trick 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/ [...]
SteveS said,
August 14, 2008 at 4:10 PM
I tried this trick: it worked, but returned 12/30/1899 as today’s date!?
Christophe said,
August 15, 2008 at 3:01 AM
SteveS, in step 4 you HAVE TO delete the “Today” column, though this is not explicitly mentioned in the post.
bill said,
September 2, 2008 at 11:43 AM
I tried this trick, it worked, but it don’t change when it is next day.
Ryan said,
November 22, 2008 at 1:40 PM
Afraid I have to tell you that this work around DOES NOT WORK in WSSv3/MOSS. It will not update, see http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ for details.
Kumar said,
January 5, 2009 at 1:38 AM
Thanks man for this trick – it worked!
Scott H said,
January 16, 2009 at 1:48 PM
Thanks for the great tip! My use for this: I wanted a SharePoint task list view that separates the tasks that are assigned to the person viewing the page versus the ones assigned to anyone else, without filtering them out. I created a column of type “text” called AssignedTo-Text that I had to use Workflow to fill with the value of AssignedTo (which also you can’t use in a calculated column). As the trick describes, I created a text column called “Me.” I then created a calculated boolean column called “AssignedToMe,” with this as the rule:
=([AssignedTo-Text]=Me)
I then deleted the “Me” column as described, and in the task list view, I set it to ‘Group By’ “AssignedToMe” in descending order (so AssignedToMe=Yes is on top) with expanded columns.
Scott H said,
January 16, 2009 at 2:38 PM
I retract my comment. Apparently this doesn’t work dynamically. As Bill said above, the [Today] value doesn’t change the next day, and likewise, the [Me] trick doesn’t work for the next person. The calculated value only recalculates after changes in the list item, not after changes in [Today] or [Me] (ie, changes in the date or viewer). You’d be better of using the “Modified By” and “Modified” fields if you only need static calculations. Otherwise, you’re out of luck once again.
Kurt Hudson said,
January 29, 2009 at 4:45 PM
If you delete and then create the dummy Today field each day, then all the fields that use the Today calculation in the site are updated. The trick is finding a way to automatically create/delete the Today column. Right now I am doing that manually, but I am always looking for a way to automate that from the client side. I cannot automated from the server side because I don’t have administrative access to the server.
Joe Gugel said,
March 4, 2009 at 5:27 PM
Excellent trick! Did a quick search for the error text, this was the first result – exactly what I needed!
Thanks
Praful Goyal said,
April 2, 2009 at 9:26 PM
I works for me also. Thanks.
mrkimmy said,
September 10, 2009 at 3:54 AM
Excellent trick!
Thanks
Bill Bartmann said,
September 11, 2009 at 4:51 AM
Great site…keep up the good work.
Dryer Vent Cleaning said,
September 19, 2009 at 10:08 AM
Excellent site, keep up the good work
arrodilfeld said,
September 22, 2009 at 8:05 AM
I don’t know If I said it already but …Hey good stuff…keep up the good work!
I read a lot of blogs on a daily basis and for the most part, people lack substance but, I just wanted to make a quick comment to say I’m glad I found your blog. Thanks,)
A definite great read..
-Bill-Bartmann
Tony Brown said,
September 24, 2009 at 11:37 AM
I don’t know If I said it already but …I’m so glad I found this site…Keep up the good work I read a lot of blogs on a daily basis and for the most part, people lack substance but, I just wanted to make a quick comment to say GREAT blog. Thanks,
A definite great read..Tony Brown