I need to calculate date based on other column I'm using =Date(Year([endDate]),1,1) in a calculated field which needs to return data as DateTime, but it is giving me error:

The formula contains a syntax error or is not supported.

After that I tried to modify the view to take only the year from today date as a string like: Trim(Text(Year([Today]))) and it is accepting the formula, but no results in the view.

Any suggestions what can be causing the problem? I prefer to make it work like in the 1-st option which I described, but if it is doable in the 2nd, it is also acceptable.

  • Which Language are you using on SharePoint Server? I have had a problem with the ',' in previous project. In some language packs the delimiter seems to be a ';' (semicolon) instead of a comma. Maybe it helps :)
    – ReTech
    Oct 25, 2019 at 9:27

2 Answers 2


In order to create a calculated column as a date you need to use your formula in below format:


Using the formula as you have added in question, I am able to create a calculated field which will be in date format. see below:

enter image description here


  1. I have return this field as Date and Time (highlighted in above image).
  2. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language on your site). So in that case use semicolon(;) instead of comma(,).

Reference: Date and time formulas.

  • You were right. Maybe because of the regional settings, I should separate parametes by ';'. Thanks u a lot, u saved my day Oct 25, 2019 at 10:03
  • Welcome. Glad it worked for you. Keep using this platform to ask any queries/questions. Oct 25, 2019 at 10:20

I tested in my end and the column works just as Ganesh showed. Also i tried with semicolon which pops with syntax error. Besides the possible language reason, try with a new column or new list to see if the issue remains.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.