I was looking into a problem with a calculated column in a SharePoint 2007 site.
On one environment the formula worked fine. On another, it kept throwing “The formula contains a syntax error or is not supported” errors.
The formula does something like:
- =IF([Status]="Done",[Date Done]-[Due Date],"")
Because I got this great error that told me exactly what was wrong I started troubleshooting by stripping the formula to eliminate possible causes till the formula looked like:
This still generated the same error message.
After searching for a while I found the following post:
http://www.hezser.de/blog/archive/2008/10/12/programmatically-creating-a-spfieldcalculated.aspx
In this post by René Hézser mentions the fact that he had a problem with an IF statement in a calculated column because his site locale was German. So I went back to check and indeed, on the environment where the formula threw the error the locale of the sites was Dutch. Changing the locale to English solved the problem, so with that the cause of the problem was clear. Unfortunately the locale of the environment is Dutch for a reason and the customer doesn’t want an English locale on their sites.
But not to worry, after all, all I had to do was find out what the Dutch equivalent of =IF([Status]="Done",[Date Done]-[Due Date],"") was. After doing a Bing search I found out that the Dutch equivalent of IF in Excel is ALS, so I tried the formulas =ALS([Status]="Done",[Date Done]-[Due Date],"") and =ALS([Status]="Done";[Date Done]-[Due Date];""), which both didn’t work.
When I was about ready to give up I decided to change the site locale back to English, fill in the original formula and change the locale back to Dutch. Brilliant brainwave…after opening up the site column again the formula changed to:
- =IF([Status]="Done";[Date Done]-[Due Date];"")
So, if anyone is looking for the Dutch, or Nederlands, or 1043 (just trying to help out people searching for this here) equivalent of the IF statement in SharePoint 2007 it’s IF, but it uses ; instead of ,.