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.





    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 (20)

  1. James Kemp says:

    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  

  2. James Kemp says:

    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 šŸ™‚

  3. James Kemp says:

    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

  4. James Kemp says:

    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

  5. James Kemp says:

    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

  6. James Kemp says:

    Hi Brian,

    I don’t think this would be possible out-of-the-box, you would need to write some custom code. The alternative would be to:

    – Have the text field always available when users create a list entry

    – Create two different views on the list to show/hide the extra field depending on whether or not choice X was chosen. Not ideal perhaps….

    Thanks,

    James

  7. James Kemp says:

    Hi Debbie,

    I don’t think this is possible out of the box. One would have to do some custom coding instead. I would also check out http://www.codeplex.com and see if you can find anything.

    Kind Regards,

    James

  8. Debbie Robinson says:

    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

  9. Mikael Söderström says:

    Hi James,

    Thanks, that´s exactly what I was asking for. šŸ™‚

    Regards,

    Mikael Söderström

  10. Nikoli Plantov says:

    Brilliant. Thanks for the information.

    Nikoli Plantov

  11. Sergei Golubev says:

    Thanks, James! Your article was very useful for the task Iā€™m doing now.

  12. Brian says:

    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

  13. Jan Munkholm says:

    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

  14. mike says:

    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

  15. Andy G says:

    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!

  16. Nate says:

    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.

  17. Esther Hunter says:

    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

  18. Joe Lichty says:

    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?

  19. LoriH says:

    Thanks for the great post!! I’ve been hunting for this information for 2 weeks – now I came across it looking for something else. šŸ™‚  

Skip to main content