Functions for use in a MOSS 2007 column ([Today] [Me] [Other??])

  • [アーティクル]

Following the recent questions from my last post I thought it would be useful to highlight what functions are available when creating a column in a list/library within MOSS 2007. This can be useful when creating a:

  • Calculated column based on other columns within a list/library. For example, to calculate the number of days difference between a list item being Created and Modified.
  • View of a list/library that shows items that meet a certain criteria. For example, a View showing the tasks assigned to me.

Although the [Today] and [Me] functions are well know, other functions are not.

List of functions available

All of the functions available are listed below. I have only tested a sample of these in a MOSS 2007 list, but in theory most/all should work!

To get a more detailed list of the functions available:

  1. Open a MOSS 2007 site.
  2. Create a list.
  3. On the Actions menu, select Edit in Datasheet.

   4.  At the bottom of the list, click the Help hyperlink. This will open a chm file.

1.
    5. In the chm file, expand the Function Reference tree

 

 

 

 

The chm file may also be found at C:\Program Files\Microsoft Office\Office12\1033\STSLIST.CHM.

Example use of a function

Requirement = calculate the number of days difference between two date columns.

1. Make two date columns, one called Date1 and the other called Date2.

2. Make a third column (e.g. DateCalc) to calculate the difference and ensure the column type is set to Calculated.

3. In the Formula box type =DATEDIF(Date1,Date2,"D")

4. In the The data that is returned from this formula is section, click the Number radio button.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Result within a MOSS 2007 List:

 

 

Enjoy!

Comments

  • Anonymous
    January 01, 2003
    Hi Joe, It sounds like: A) If TODAY is used in a column without a calculation, it won't be updated dynamically B) If TODAY is used in a calculated column, it isn't allowed. So, the only two options left I think are: (C) to use the TODAY function in a view, and see if that achieves your goals - see http://blogs.technet.com/collaboration/archive/2008/03/19/how-to-see-items-added-to-a-sharepoint-list-library-that-meet-a-custom-date-value.aspx, or (D) to create  a custom field and use the TODAY value within it. MSDN guidance on this is available at http://msdn.microsoft.com/en-us/library/ms446361.aspx Thanks, James  

  • Anonymous
    January 01, 2003
    Hi Esther, The calculations should be dynamic in most/all cases. I would have to test the TODAY function to be sure for the example you have given. Please share your results if you have tested this already. James :)

  • Anonymous
    January 01, 2003
    Hi Mike, This is slightly different to the scenario that Jan posted and unfortunatley I'm not sure this is possible OOTB. Instead, you would probably need to create  a custom field. MSDN guidance on this is available at http://msdn.microsoft.com/en-us/library/ms446361.aspx Thanks, James

  • Anonymous
    January 01, 2003
    Hi Jan, The only way I can think of doing this would be to:

  • Create the list/library
  • Create a column (i.e. Column X) which will hold the choices available to users (e.g. Choice 1, 2 and 3). Make Column X hold values of type 'hyperlink'
  • Create three view on the list/library for each of the choices available. For each view, you can then define whatever columns, calculations and sorting you want
  • Make the hyperlink in Column X correspond to the different views of the list/library. This should mean that when a user clicks a choice in Column X, it just refreshes the page and shows a different view of the same list/library. To them, it will appear as if the list/library has changed based on their actions. I hope this helps? James

  • Anonymous
    January 01, 2003
    Hi Andy, I had a small experiment and came to the same conclusions as you have. The only possible away around this that I can think of is to modify the list/library so that you can manage the content types on the list/library. Then, on your custom content type you should be able to delete/modify the Title column to be something else (e.g. a calculated field). You should be able to do the formulas you desire on this "default/first" field. Good luck! James

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    Hi Nate, I'm not 100% sure what you are after. Do any of the following posts help: http://blogs.technet.com/collaboration/archive/2008/03/19/how-to-see-items-added-to-a-sharepoint-list-library-that-meet-a-custom-date-value.aspx http://blogs.technet.com/collaboration/archive/2008/02/22/how-to-see-items-added-to-a-sharepoint-list-library-in-the-last-x-days.aspx Thanks, James

  • Anonymous
    January 01, 2003
    Is there a way to make a column appear in a list with only 15 characters showing in the column and the rest wrapped like you can in an excel spreadsheet

  • Anonymous
    March 31, 2008
    Hi James, Thanks, that´s exactly what I was asking for. :-) Regards, Mikael Söderström

  • Anonymous
    May 15, 2008
    Brilliant. Thanks for the information. Nikoli Plantov

  • Anonymous
    June 09, 2008
    Thanks, James! Your article was very useful for the task I’m doing now.

  • Anonymous
    August 22, 2008
    Hi, I have Choice Column that is a drop down menu.  I want to display a text field when an item in a list has been selected. Do you know how I go about this? Thank you

  • Anonymous
    September 16, 2008
    Hi James Can you calculate or sort the content of a column based on which content that the user has chosen in another previous column? Br. Jan

  • Anonymous
    October 06, 2008
    James Is it possible to have text content dependent on a previous selection? For example - have two drop down columns on a library, 'Vendor' and 'Product', the selection of a Vendor changes the visible choices in the Product column; for example choosing a Vendor 'Microsoft' would only list Microsoft products in the Product drop down. Thanks very much

  • Anonymous
    November 06, 2008
    Hi James, Great post! However, from this I'm assuming that what I want to achieve isn't supported. Or at least, isn't obvious. I want to get the currently logged in users name into an existing default calendar 'Title' column. Since this default column can only be either text or a calculation, options are limited. The formula [Created By] doesn't exist in the above reference table, neither does [PreferredName](it was an idea from the UserProfile Service). Trying to get a duplicate of a 'People or Group' column by using [column_name] also fails. All of the above return the error: 'One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.' Using [Me] which is the closest option returns 'DOMAINusername' which is not particularly pretty. How can I get this to format like 'firstname lastname' within the calculation? If you have any ideas I'd be pleased to hear them!

  • Anonymous
    November 26, 2008
    How can I use functions in View Filters? I can use all these in a calculated column. However, I want to have my view display items in a list based on a computed "Week" value based on a formula: FLOOR((([Julian Date]+1)/7),1). I want to compute the Julian date from [Today] in my filter then run my computation on it, yielding my actual week. This way, I see only items for the current week (forward and back), and none for the following or prior weeks.

  • Anonymous
    January 22, 2009
    Thanks so much for this post. Very helpful. One question is whether or not this calculation will constantly update to reflect the current date. I think that current date (TODAY) is stagnant no? thanks, E

  • Anonymous
    February 11, 2009
    Hi James-- This helped quite a bit, but I'm having some challenges: I need to constantly calculate the time to expiry for some domains based on today's date and the expiry date. When I try your solution, the calculation is correct but the date in the record does not reflect the current date, only when I altered that record. If I try to use TODAY in a calculated column, I get a note saying volatile functions such as ME and TODAY cannot be used. Any ideas?

  • Anonymous
    February 19, 2009
    The comment has been removed