Gotcha! DATE / DATUM calculated formula column in SharePoint
A colleague of mine had a question about a calculated column. He wanted to link an expire date to documents in a document library, but users would have to choose a retention period of 1-10 years.
Not that hard if you create two site columns; one of type ‘choose’ which presents the options 1-10 in a dropdownlist. The second one would have to be a calculated one with the following fomula:
=DATE(YEAR([Created])+[RententionTime],MONTH([Created]),DAY([Created]))
So far, so good. But when we wanted to deploy in a production environment at a customers’ site, SharePoint started complaining it didn’t understand the formula.
Seems the same trick which applies to Excel also goes for SharePoint: formulas have to be translated. And no, not only the names of the columns, but also the names of the methods and even the syntaxis is different. We ended up with this (in Dutch):
=DATUM(JAAR(Gemaakt)+[Bewaartermijn in jaren];MAAND(Gemaakt);DAG(Gemaakt))
Notice that even the comma’s are replaced by semicolumns; it won’t work otherwise. You can use a localized version of Excel to check the syntaxis; it seems to be the same (although not all methods are supported offcourse).
Leave a Comment