Calculated columns: the (useless?) “Today” trick

Posted on by

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.

53 thoughts on “Calculated columns: the (useless?) “Today” trick”

  1. Pingback: EndUserSharePoint.com: Resource for Calculated Columns : End User SharePoint

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  29. Pingback: MasterPage/PageLayout format date field « PANVEGA’s Blog

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

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

  32. Pingback: Useless calculated column today trick – XSL today() to the rescue. « Sharepoint applied

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

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

    • 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);

  35. How can I get the “Date Created” column to default to current time like it does current date. In the additional column settings section I have selected the column to be required, Date and Time format, and Today’s date. I am on MOSS 2007 and tried several times to calculate the column but was unsuccessful.

    I need to have a unchanging create date/time column so that the appovers can apply a First Come First Serve rule for reservations. When the time defaults to 12 AM 00 they can’t distinguish between requests entered on the same day.

    Also, we have seen the Created column display value change when editing and event to add a reoccurrence setting. It appears to set the Modified and Created columns to be within a minute or two on the Edit Item page but the original Created column actually contains a different earlier date.

    Any help is much appreciated.
    Michael

  37. Here is my workaround to refresh Today without programming

    First of all, sorry about my poor english.

    I spent days looking for a solution for “Today” issue. I don’t know anything about programming, I am system admin so It was kind of hard.

    So this is my workaround to solve it using the dumb column “today” and the fantastic extra functions for stsadm.

    1.- Download wsp for custom properties of stsadm
    http://stsadm.blogspot.com/

    2.- In any list create field “Today” manually

    3.- Export field “Today” into a xml file:

    stsadm –o gl-exportlistfield -url “”http://server/site1/SourceList/AllItems.aspx” -fielddisplayname “Today” -outputfile “c:\Today.xml”

    4.- Import and delete column “Today” into your site. It will update your values

    stsadm -o gl-importlistfield -url “http://server/site1/List1/AllItems.aspx” -inputfile “c:\TodayCol.xml”

    stsadm -o gl-deletelistfield -url “http://server/site1/List1/AllItems.aspx” -fielddisplayname “Today”

    5.- Create BAT file with the point 4 and schedule it to run once on weekdays.

    PS: In case you don’t know how work with BAT files:
    run CMD

    then run this commands:
    ___________________________________________________________

    cd\
    cd:

    copy con refreshToday.bat

    Echo. Refresh column today

    stsadm -o gl-importlistfield -url “http://server/site1/List1/AllItems.aspx” -inputfile “c:\TodayCol.xml”

    stsadm -o gl-deletelistfield -url “http://server/site1/List1/AllItems.aspx” -fielddisplayname “Today”

    (Ctrl +Z)

    schtasks /create /tn “Refresh Today” /tr c:\refreshToday.bat /sc daily /mo 24 /st 01:00:00 /sd 12/15/2009

    exit
    ___________________________________________________________

  38. Wow! Love the article! Almost the same thing I am trying to create. I am trying to use a formula I believe to update a column?

    i.e.
    Expiration Date > 30 then Expiration Date = 30

    I want it to update the column or use the Actual Expiration Date?? I don’t want articles posted by other users to last longer than 30 days that is my goal.

    • If ‘Expiration Date’ > 30 then ‘Expiration Date = 30’ else ‘Expiration Date’=’Expiration Date’

    • Kris, rather than using calculated columns, how about setting up a filter in your list view? Something like:
      Created
      more than or equal to
      [Today]-30
      ([Today] doesn’t work in calculated columns, but you can use it in view filters).

  39. Pingback: Sharepoint: Filtering with Web Part Connections | EndUserSharePoint.com

  40. I need help with filtering using today as criteria, but I need to apply it to a numeric field not a date.

    I have list with facilities that need to report info each day and the data is kept for six days. I prepopulated the list with each facility six times and added a day of week column with entries of 1,2,3,4,5,6 corresponding to each of the six records per facility. So far, so good.

    But…I want my default view filter to be dynamic based on the day of week Today is. i.e., if today is Tuesday, filter the list where the day of week column = 3. Before you mention it, I can’t just filter on Modified = Today because I need to see both the facilities WITH entries and facilities WITHOUT entries (failure to respond on the current day).

    I know I could change the view filter each day manually, but I’m really looking for a way to make it dynamic.

    Any help would be greatly appreciated!

  41. Thank you for your posts about the Today trick’s uselessness as well as how to “Highlight rows in SharePoint lists”. Like others who have replied here, I was attempting to compare dates in my list with Today to determine if they were “past due” and if so, by how long. The goal was to display color coding of list entries in a view on a “dashboard” web part page.

    Without the ability to use the Today function – because of the static nature of the data in the list – and not wanting to perform unnecessary daily edits for the sake of making the date comparisons I turned to adding a function to the Javascript file that I was already applying to the dashboard page in the interest of displaying the content of a calculated column as HTML. I customized the content of the calculated column’s HTML to incorporate a call to the custom javascript function and pass in a date from the list in String format (ex. “5/12/2011”). The javascript function then performs the date calculations, because it CAN use a “today” function (Date()) and returns a hexidecimal number code used by the calculated column’s HTML to be used in that list row on the dashboard view.

    I think that using a javascript file to handle “today” date calculations is the least complicated method if your goal is to display data from a list on a web part page.

    I hope you find my comments helpful.

    • Paul, I fully agree that JavaScript is a convenient way to handle the Today issue.
      If you search my blog for “countdown”, you’ll find a couple articles that rely on JavaScript.

  42. Hi All, I encountered the disappearing bracket issue today on a custom list in a calculated column.
    Although the available columns list does not show ID, I am still able to use [ID] just fine in the calculation,
    =CONCATENATE(“http:://Myurl/MyPage.aspx?ID=”&[ID])
    … but as soon as a row is edited, the brackets get removed in the calculated column definition and I need to go into the formula and re-add the brackets around ID and it works again. All other rows are fine.
    Any ideas why this occurs?

  44. Pingback: markwilson.it » Calculated value based on today’s date in a SharePoint column

  45. Pingback: SharePoint: Filtering with Web Part Connections | test

  46. Pingback: SharePoint: Filtering with Web Part Connections |

  47. Pingback: Configure Document Review Process Using DVWP and XSL | The SharePoint Effect

Comments are closed.