Use [Today] and [Me] in Calculated Column

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.

  1. Open up the List Settings page where you want this column.
  2. Create column named “Today”. The type doesn’t matter here, so let type as “Single Line of Text” and just click “Ok”

  3. Now create the calculated column where you need to use current date within formula and add “Today” from available columns

  4. 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

About these ads

48 Comments

  1. May 22, 2008 at 1:00 PM

    [...] Use [Today] and [Me] in Calculated Column [...]

  2. Mano said,

    June 5, 2008 at 7:00 PM

    Great .. thanks :)

  3. 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/ [...]

  4. SteveS said,

    August 14, 2008 at 4:10 PM

    I tried this trick: it worked, but returned 12/30/1899 as today’s date!?

    • Hamza Farooq said,

      April 2, 2011 at 4:36 PM

      Im facing same problem. getting this
      12/30/1899 4:00 AM

      Farmula i used is =[Start Time]-Today

      • Andrew said,

        April 20, 2011 at 1:24 PM

        Is there any other way?
        (=[Start Time]-Today) does not help, SP shows 30.12.1899 :(

  5. 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.

  6. 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.

  7. 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.

  8. Kumar said,

    January 5, 2009 at 1:38 AM

    Thanks man for this trick – it worked!

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. Praful Goyal said,

    April 2, 2009 at 9:26 PM

    I works for me also. Thanks.

  14. mrkimmy said,

    September 10, 2009 at 3:54 AM

    Excellent trick!

    Thanks

  15. September 11, 2009 at 4:51 AM

    Great site…keep up the good work.

  16. September 19, 2009 at 10:08 AM

    Excellent site, keep up the good work

  17. 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

  18. 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

  19. dung2 said,

    February 11, 2010 at 11:45 AM

    woooowwww…. thanks dude! it save my ass… :D

  20. Barb said,

    April 27, 2010 at 3:16 AM

    I don’t understand why this is not working for me. I’m still getting the error.

  21. Sarwa said,

    September 30, 2010 at 6:36 PM

    Dear ,

    You really saved my time, I have wasted around 1 hour to get today’s date. You are really awesome… thanks dude.

  22. Bazin Makonnen said,

    October 16, 2010 at 12:37 AM

    If you want this to update daily, simply create a workflow using Sharepoint Designer so that it updates a dummy field. This will modify the record and capture the correct system date. The trick here is that the workflow has to run everyday preferably a little after midnight to ensure that the date is captured correctly.

  23. Jill said,

    March 25, 2011 at 2:42 AM

    I’m new to SharePoint so I’m on a huge learning curve! I need to create a formula that does a rolling count of how many days a task is open. I have a “Resolution Time (Days)” that will count the number of days it was open for.

    I’m guessing the fields I would need to incorporate into the formula would be the dates of “Log Received” and somehow the Current Date with I don’t have a column for.

    Please help!

  24. Hamza Farooq said,

    April 2, 2011 at 4:35 PM

    Who posted this first?

    http://bborole.blogspot.com/2010/10/calculated-columns-cannot-contain.html

    wordings are same

    • April 3, 2011 at 12:44 PM

      Thanks for this. Some guys really don’t know what does team works means. They just copy some one else findings and put it on their credits. . They dont even know what does link back or post reference means. They will not realize this until someone else will do same with their findings. Better see the date of posting, and comments date.

  25. Guest said,

    July 21, 2011 at 1:59 PM

    Hi

    Thanks for this. It help me a lot.

  26. Nick said,

    November 18, 2011 at 2:59 PM

    Hi,

    I use the [Today] trick quite often, as stated you need to delete the [today] field to get the correct result from the calculations…

    To refresh all the calculations, just create a column called ‘Today’, it doesn’t matter what type, after you have done this just delete the ‘Today’ column, this will recalculate all the calculations…

    Can anyone create a HTML script to do this evry 24 Hrs ???

  27. Daniel said,

    January 25, 2012 at 10:06 PM

    Hi

    If only this did work in SharePoint 2010
    The created “Me” col used in my “IsUser” Calc text col =[Me] defaults to a value: ‘0’
    The created “Today” col used in my “CalcToday” date col =[Today] defaults to a value: 12/30/1899

    I think the SharePoint dev team of got wind of this and have stopped it.

  28. Roshan Tated said,

    May 3, 2012 at 2:15 PM

    Wow..wonderful post..just fooled Sharepoint..;)

  29. Corey said,

    July 10, 2012 at 9:30 PM

    In SharePoint 2010, the now() function seems to work fine: =IF([Art Due Date]>=NOW(),”On Time”,”Late”)

  30. July 30, 2012 at 8:38 PM

    [...] This post has more information. Share this:TwitterFacebookLike this:LikeBe the first to like this. [...]

  31. Loopy said,

    November 30, 2012 at 11:34 PM

    Great tip, thanks! I should have checked here before… :-)

  32. December 22, 2012 at 7:35 PM

    It’s really a nice and helpful piece of information. I’m satisfied that you just shared this helpful information with
    us. Please stay us up to date like this. Thanks for sharing.

  33. larabsky said,

    May 29, 2013 at 2:50 AM

    awesome!! thank you

  34. RichT said,

    May 31, 2013 at 11:20 PM

    Appreciate you posting this but I agree that the workarounds aren’t ideal. #22 says to create a timer job for a workflow – not so great if you’re not a Sharepoint Admin. I can create the workflow but don’t know how to trigger it. Does anyone know how else I could?

  35. valygreavu said,

    June 22, 2013 at 3:07 PM

    In SharePoint 2010 it is not necessary to create a new column for Today!
    It is enough to create a new Calculated field (eg. Days Remaining) with formula: =[Estimated date to close the project]-TODAY()
    It is better to use TODAY() function instread of creating a new column!

    • p said,

      September 4, 2013 at 11:27 PM

      I am using sharepoint 2010 and I have tried to use ” today()” and it works just for that day. But everyday i need to edit the item (open, do nothing, and then save) in order that the date change automatilly.

  36. Tom said,

    September 18, 2013 at 5:25 PM

    You can use a PowerShell script to update a field in a SharePoint list set at either 5 mins to start of everyday to give you current date and time.

    cls
    Add-PSSnapin Microsoft.SharePoint.Powershell
    $web=get-SPWeb “http://sitename/”
    $web.title
    $list=$web.Lists["Your List"]
    $list.title
    foreach($item in $list.Items)
    {
    $item["Duration"] = “$Get-Date”
    $item.update()
    }
    $web.dispose()

    Then set it to run in task scheduler, and make sure you set the field that’s being updated to a date time field and chose whether you want just the date or date and time

  37. web page said,

    February 8, 2014 at 4:44 AM

    Regularly scheduled meals (every few hours) have proven more wholesome than infrequent, haphazard ones.
    Formative assessment – Formative assessment is normally completed
    in a course or project. * Ability to change others perceptions by democratic means.

  38. spsps said,

    April 21, 2014 at 8:11 PM

    http://sharepoint-2013-solutions.com/product/sharepoint-2013-today-field-updater-without-starting-workflow/ – this is bes solutions to your problem. To good work, must daily update all items with field.

  39. April 23, 2014 at 8:32 PM

    […] If you're using a calculated column in any of your filters and want to filter by [Today] or [Me], you'll need to utilize this classic workaround. […]

  40. October 6, 2014 at 6:53 PM

    […] I have read those as well here is the link I was working off of yet still does not work http://abstractspaces.wordpress.com/…ulated-column/ Reply With […]

  41. Nikolai said,

    October 8, 2014 at 6:17 AM

    Use a shell script above by Tom. Post #38.
    But update the DESCRIPTION field in the list itself at 12:00AM.
    Updating DESCRIPTION will update the [Today] value.

    midnight.ps

    ***

    # Working script for Complete Travel Log List.
    # We need to update “TV Status” column to refresh Today()
    # Otherwise Outstanding travel reports will not work.

    $spWeb = Get-SPWeb “http://intranet/travel” -AssignmentCollection $spAssignment
    $spList = $spWeb.Lists["Travel Log List FY2014"]
    $fileds = $spList.Fields["TV Status"]
    $fileds.Description = “Last Update: ” + (Get-Date)
    $fileds.Update()
    $spWeb.Dispose()

    ***

    $fileds.Description = “Last Update: ” + (Get-Date) – < This line makes the trick!

    Всем привет!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: