05 February, 2011

The formula contains a syntax error or is not supported. Troubleshoot issues with windows SharePoint services.

Hello Everybody,

This is regarding the above mentioned error message. Working on SharePoint from last few years, i had seen customers used the date functions on regular basis inside their SharePoint list and libraries.

Depending the same, i worked on similar case which was weird in the start but finally the issue has been  resolved with some perfect break-fix.

Let me describe the issue first:

-User had created one custom list.


-Created three columns: two were custom and one is calculated.
    Custom Columns:  Date1 and Date2 with content type Date and Time.
    Calculated Column:CalcDate with content type calculated.

-User wanted to use basic date functions as difference between two dates, TEXT Function,DATE(year,month,day) etc...

-Custom columns has been created without any issues but when he were using a formula inside calculated column then he got the following error message: 

Troubleshooting done:

-Created a same list on my personal mysite and used the same functions, it worked without any problems...
-Compared features between my site and user's site. Found only difference i.e. publishing feature was activated on my site but wait wait....this is of no use because after deactivating the publishing feature also, i can use the same date functions.
-We have site collections created under so many domains, so i had used a site collection which belongs to different domain & still i can use the date functions without any issues...
-I had used different site collection which belongs to same domain which is used by user also, and here also i can use without any problems...

Workaround:

-Save the SharePoint list as template on which we can use the date functions without any problems.

Next step is very simple, go to the affected site collection list gallery and upload ( please refer the following steps):

-open the site collection
-site actions
-site settings
-Galleries
-List Templates

Resolution:

You just have to use ";" instead of "," it works fine without any issues..

Examples:
+Calculate the difference between two dates:

=DATEDIF([Column1]; [Column2];"d")

Please refer the below mentioned Microsoft published article which is excellent as it contains a basic functions that we can use in SharePoint.
 

http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx?CTT=3

If you face any queries /questions regarding the above mentioned procedure/information then please let me know...

I would be more than happy to help you as well as resolves your issues. Thank you very much once again :-)