onlydarksets

Just another WordPress.com weblog

Using [Today] and [Me] in SharePoint calculated columns

Posted by onlydarksets on March 20, 2008

Once again, I have nothing to add to this great tip:

http://www.sharepointblogs.com/mkruger/archive/2007/06/26/using-today-in-a-calculated-formula-birthday-lists.aspx

  1. First, create a new column in your list with the column name of “Today”.  
    Click “OK”.  (It doesn’t matter what type of column or data it is, this is
    just a place holder and will be removed later).
    MK Note: Create column called Today… I also created a column called DOB (date field) where I will enter the birth date.
  2. Next, create a column with the data where you would like to use “Today” as a
    reference to today’s date.  The column type should be “Calculated”.  In the
    formula field, create your formula using “Today” as if it held the column date/time.
    SharePoint will calculate the formula based on the assumption that you will
    be using “Today” as a reference to the new column you just created.
    MK Note: I created a column called MonthCheck with the formula shown below:
    =IF(MONTH([Today])=MONTH([DOB]),”Birthday”,”NotBirthday”)
  3. Next, edit the new column named “Today” that you created in the first step.  
    On the very bottom of the page, click “Delete”, to delete the column.  In
    your formula, SharePoint will keep the reference to “Today” but it will
    change from referencing your column, to a reference to the current date.
  4. MK Note: Now you can create your new view in the list to display “This Month’s Birthdays“.  Just apply a filter to Show only when the following is true: MonthCheck is equal to Birthday
    I’ve also created a BirthDay Column (Capitalized the “D“ to note the difference) as well that is a calculated column: =DATE(YEAR(Today),MONTH(DOB),DAY(DOB))
    You can then create a view to display Today’s Birthdays by filtering to Show only when the following is true: BirthDay is equal to [Today]

4 Responses to “Using [Today] and [Me] in SharePoint calculated columns”

  1. Christophe said

    On my blog I show an easier way to achieve the same result as this “Today” trick:
    http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

  2. Thanks – I’ll try it out!

  3. Ryan said

    I am trying to put the record straight that this 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 and some workarounds.

  4. Well, to be clear – it works, but it doesn’t update automatically. I think most people are aware of that. For my purposes, that is acceptable. There is a way to do it dynamically with javascript at the client, but that’s a bit overkill for my application.

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

 
%d bloggers like this: