locked
SharePoint Calculated Column RRS feed

  • Question

  • Hi

    I have a issue with calculated column in document lib,

    There is one column NAME which has documents associated with it. The document has naming convention as TEXT and DATE

    example:  ABCD20110505  [[ABCDyyyymmdd]

    I would like to create a calculated column which has to get the date from the document's name

    Is this possible in MOSS 2007? if so pls advise me how to do that?

     

    Thanks

    • Moved by Mike Walsh FIN Friday, May 6, 2011 6:05 AM The Calculated Column is OOB and thus questions on it go to Admin (From:SharePoint - Design and Customization (pre-SharePoint 2010))
    Thursday, May 5, 2011 8:38 PM

Answers

  • Hi SRPD,

    The steps in my first post can only create a column in the data view web part, but it had never created a new column in that library. If you want to use this column to filter another web part, please try this.

    1. Create a new column use to copy the content in Name column (We named it Copy Name).
    2. Create a workflow start when an item is created & Modified. Add a update list item action, update the Copy Name field with the value in Name colulmn.
    3. Create a calculate column and type formula RIGHT([Copy Name],8), select Date and Time type returned from this formula.
    4. Use this calculate column to filter reports.

    Check how it works.

    Best regards,
    Emir

     

    • Marked as answer by Emir Liu Monday, May 16, 2011 2:15 AM
    Thursday, May 12, 2011 12:43 PM

All replies

  • =RIGHT([NAME] ,8)

    that should do it.
    • Proposed as answer by Parvez Akkas Friday, May 6, 2011 1:21 AM
    Thursday, May 5, 2011 10:33 PM
  • NAME column is not provided in the insert column list.

    still i tried placing it, it shows error, The formula refers to a column that does not exist.

    How to add all the columns existing for the insert column option?

    pls help in this

    Thanks

    Friday, May 6, 2011 1:30 PM
  • Hi SRPD,

    Name column couldn’t be got in calculated formula, so you can create a workflow, update another column with the content in Name column, and then use that duplicate column instead. Or if you only want to get the date and display it in a column, you can insert a meaningless column follow this step.

    1.       Open the site in SharePoint Designer, find the library and open AllItems.aspx page.

    2.       Right click list form web part and click Convert to XSLT Data View.

    3.       Right click Data View > Inert > Column to the right.

    4.       Right click the new column > Insert Formula.

    5.       Type the following XPath expression: substring(@FileLeafRef,5,8) > Click OK.

    6.       (Type a column name in the first cell of this column).

    This could only work when the length of the text is 4, otherwise you can do a litter modification for the XPath expression.

    Best regards,
    Emir

    Monday, May 9, 2011 10:26 AM
  • Simply great Emir

    i got the text but its not showing in date format

    from document name ABCD20110505 , by appling this i got a new column with 20110505 as text

    but its not shown in date format

    I have to use this date to filter reports in web part pages.

    Its displayed as only text, pls help in resolving this

    Thanks

     

    Wednesday, May 11, 2011 3:18 PM
  • And one more problem that im facing is.. i have a column with this formula inserted thr SPD

    its properly working in All DOduments view

    when im trying to create views i see the column but i dont see the content in it.. the formula is not been executed, pls suggest on this

     

    Thanks

    Wednesday, May 11, 2011 3:30 PM
  • Hi SRPD,

    The steps in my first post can only create a column in the data view web part, but it had never created a new column in that library. If you want to use this column to filter another web part, please try this.

    1. Create a new column use to copy the content in Name column (We named it Copy Name).
    2. Create a workflow start when an item is created & Modified. Add a update list item action, update the Copy Name field with the value in Name colulmn.
    3. Create a calculate column and type formula RIGHT([Copy Name],8), select Date and Time type returned from this formula.
    4. Use this calculate column to filter reports.

    Check how it works.

    Best regards,
    Emir

     

    • Marked as answer by Emir Liu Monday, May 16, 2011 2:15 AM
    Thursday, May 12, 2011 12:43 PM