Thursday, September 27, 2007

SharePoint "calculated" field "TODAY"

Very usefull I test it (from somebodyes post):
It is possible to use "Today" in a function as a reference to today’s date
(despite what SharePoint tells you). There is a very simple work around that
doesn't involve very much effort or complexity.

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


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


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.


This can be used in any of the Date and Time functions, but I haven't tested
it beyond that.

href="http://www.brianrudloff.com/post/Sharepoint-Services-2003---Workaround-for-filtering-on-today's-date.aspx">
Notes:
If you want to edit the formula containing "Today" SharePoint will not let
you, and will give you the same error about not using Today or Me in a
function. To get passed this problem you have to temporarily create another
Today column.


SharePoint Services 2003 does not initially come with a column for Today's Date. However, there is a workaround. Chris Johnson @ msdn wrote a great article on exactly how this works. But basically, you can trick SharePoint into using Today in a function and create whatever calculated field you need.

I recently had to use this technique to filter a SharePoint Calendar. I wanted it to display only upcoming events and out of the box, the calendar displays everything. Here's what I found:

How to Filter a SharePoint Services 2003 Web Part Calendar to Only Show Upcoming Events
1. Create a new column with the Column Name: Today (It doesn't matter what the type is, so leave it default)

2. Next, create a column with the Column Name: Begins. In the formula section, enter: =[Start Time]. The data type should be: Date and Time. The Date and Time format should be: Date Only.

3. Create a new column with the Column Name: Date_Range. (or something). In the formula section, enter: =Today-Begins. The data type should be: Number(1, 1.0, 100). Set the Number of decimal places to: 0.

4. Here's the weird part. Go back and delete the original Today column created in Step 1. I know what you're thinking...but trust me... If you had read the article I mentioned earlier, you'd understand where this is going.

5. Now Create the Calendar Web Part by Clicking on Edit Page > Add a Web Part > Check Calendar > Click Add.

6. Create the customer view by Clicking on the Calendar Hyperlink > Settings > Create View. Under "Start From an Existing View", Click Calendar. Give the Calendar View a Name and then scroll to the bottom of the page and Select "Show items only when the following is true:" In the first Dropdown box, Select: Date_Range. In the second Dropdown box, Select "is less than". Finally, enter a zero (0) into the textbox.

That's it! When you view your page, you should now have a Calendar Web Part that displays only upcoming events. If by any chance it’s still showing all events, you may have to play with the view setting for the calendar.

5 comments:

  1. Martin4:58 AM

    Hello,
    what about repeating Events?
    Calculation submit a "1" if the event is happening e.g. yesterday.
    I tried several settings but could not get a satisfying result.
    Any idea?

    ReplyDelete
  2. Hey, I am not sure what do you try to accomplish?

    ReplyDelete
  3. martin5:29 AM

    Hey,
    I try to see all upcoming events, including the repeating events.
    Your workaround works well as long as the events are not repeating and have their start time in the past.
    Do you know what I mean?
    Because then you have a calculated number that is greater than zero.

    ReplyDelete
  4. This still sounds like a cool workaround for when you want to see items in a list that were modified "Yesterday". That what I was looking for and these workarounds sound like they will work.

    ReplyDelete
  5. More about the "Today" trick on my blog:
    http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

    ReplyDelete