SharePoint User Group UK

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?

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

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

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