SharePoint User Group UK

Share the knowledge!

Welcome to SharePoint User Group UK Sign in | Join | Help
in
Home Blogs Forums Photos Files Roller

Calculated Default Value

Last post 09-02-2010, 1:04 PM by Pentalogic. 3 replies.
Sort Posts: Previous Next
  •  08-13-2010, 12:49 PM 24965

    Calculated Default Value

    I have a list with a choice field with financial years in the format 2009/2010, 2010/2011 etc. I want the default to be the current financial year, so I chose the calculated default option and used this formula.

    IF(MONTH(TODAY())>7, TEXT(YEAR(TODAY()), "####") & "/" & TEXT(YEAR(TODAY())+1, "####"), TEXT(YEAR(TODAY())-1, "####") & "/" & TEXT(YEAR(TODAY()), "####"))

    The formula works in Excel, but I get the following error in SharePoint:

    The formula contains a syntax error or is not supported.

    What am I doing wrong?
  •  08-14-2010, 11:22 PM 24970 in reply to 24965

    Re: Calculated Default Value

    Unfortunately the Today function does not operate correctly in calculated columns. I would normally recommend using the [Created] date instead but you also cannot use column references in a default value - see http://office.microsoft.com/en-us/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx.

    "You cannot reference another column in a formula that creates a default value for a column."

    I appreciate it is a not an ideal solution but in this case you could set a reminder to go change the default value on the appropriate date each year?

    Hope this helps.

    Dave
  •  08-31-2010, 2:11 PM 25051 in reply to 24965

    Re: Calculated Default Value

    As DRP says you can't use Today in calculated columns but you CAN use it in calculated default values.

    (This explains more about why - http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/)

    The problem in your formula is that SharePoint uses slightly different syntax for Today - its just Today and not Today()

    So select Create a column, name it "Financial Year" or whatever makes sense, set as "Single line of text", under *default value* choose "Calculated Value" and enter

    This is a basic hardcoded version to easier understand synatx.

    =IF(MONTH(Today)>7,"2010/11","2009/11")

    This is the full version matching your Excel formula

    =IF(MONTH(Today)>7,YEAR(Today) & "/" & (YEAR(Today)+1),(Year(Today)-1) & "/" & YEAR(Today))
    Ryan
    www.pentalogic.net
  •  09-02-2010, 1:04 PM 25071 in reply to 25051

    Re: Calculated Default Value

    I've blogged about this in a bit more detail

    http://blog.pentalogic.net/2010/09/today-sharepoint-calculated-default-values/
    Ryan
    www.pentalogic.net
View as RSS news feed in XML
Powered by Community Server, by Telligent Systems