Error while creating calculated column

Question

Hi,

We want to create a simple calculated column based on two existing date/time columns. Each time we try to add this new column, we get a "bad syntax" error message. Here is an example:

Correlation ID: b8bdcf9e-80aa-8000-dc2e-1bbbbxxxx

Date and Time: 04.04.2019 11:48:54

The formula looks very simple and it's based on official MS tutorial https://support.office.com/en-us/article/Examples-of-common-formulas-in-SharePoint-Lists-D81F5F21-2B4E-45CE-B170-BF7EBF6988B3

Example:

=TEXT([Column2]-[Column1],"h:mm")

Our formula:

=TEXT([CourseEnd]-[CourseStart],"h:mm")

Both of the date/time columns are standard. Here are the screenshots:

Can you tell us why it does not work or resolve the correlation ID given above?

Best Regards

Artur

[PII is masked by Eli Qian MSFT Support]

Answer 1

Hi Artur,

According tothis article,it mentions that All example formulas in this topic use commas "," as the parameter delimiter character. In some countries, the comma is reserved for use as the decimal mark. In such countries, users creating a calculated field must use semi-colons ";" as the delimiter character.

I think that German is the country which needs to use semi-colons ";" to replace commas "," in the formula. I also check this point on my site. I find it works when I change commas into semi-colons in the formula: =TEXT([Column2]-[Column1];"h:mm")

Hope this is clear for the situation. Feel free to let us know if it works fine on your end.

Best Regards,

Eli

Answer 2

Hi,

I found the solution, but it's very strange to me. Though the site uses only English language version, the owner changed the regional settings _layouts/15/regionalsetng.aspx to German (Switzerland). Because of this, the formulas syntax also changed, like in Excel... 

Never the less, my question is:

Is there an official documentation what are the equivalents to those special characters in all supported languages? If you open this document https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14) in Geman https://docs.microsoft.com/de-ch/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14) you will still find the same formulas that will not work in this language...

BR

Artur

Answer 3

Hi Artur,

Sorry for the delay as I was on my weekend.

Thank you for all your effort and test. Yes, there is no difference between German and English when using TEXT function from these articles. I also can reproduce this situation as your described. It seems to be malfunction but I'm confirming this point. So you could do this as workaround temporarily. I will post back and let us know as soon as I get any update from my side.

Regards,

Eli

Answer 4

Hi Eli,

I've created a brand new custom list on the same site and I'm getting the same results:

Correlation ID: 5be4cf9e-50f5-8000-da14-1xxxx

Date and Time: 04.04.2019 23:04:11

The column settings:

After this I created a new site collection (to be honest the first one is also new created few days ago) and a new list. Performed the same actions and now it works...

Are there any special settings/features that have to be enabled to make it work? I spent almost two hours to compare those two sites and can't find anything different between them :/

Best Regards

Artur

[PII is masked by Eli Qian MSFT Support]

Answer 5

Hi Artur,

I believe your formula is right. Because I can just copy and paste your formula =TEXT([Column2]-[Column1],"h:mm") into calculated column without any syntax error on my side.

To narrow down if the issue is related to your current list, could you help create a new list and test this formula again as shown in my figure above? 

Feel free to post back and let us know the result to proceed.

Best Regards,

Eli