Sharepoint 365 List - adding date

[SOE_Tech]

Hey everyone,

i am trying to create a really simple list showing all the contracts we have in a certain are.

The colums i am concentration on are

"Signed" (set as date format)

"Runtime" (set as number - in months) 

"notice period" (set as number - in months)

"earliest termination" (set as date)

I now "just" want to add "Signed"+"Runtime"+"Notice period" which constantly runs into a "formular has a syntax error..." Whatever i write in the NEWLY created list comes up with that error.

German

=DATUM(JAHR([Unterzeichnet]),MONAT([Unterzeichnet])+[Kuendigungsfrist]+[Laufzeit],TAG([Unterzeichnet]))

English equivalent

=DATE(YEAR([Signed]),MONTH([Signed])+[Runtime]+[Notice Period],DAY([Signed]))

Am i missing something? I dont understand what the Syntax Error is (and even more why NOTHING indicates WHAT it is)

Greetings

[#2]

@SOE_Tech That's strange, your English formula worked fine for me. Perhaps check the internal name of each column from the address bar when you hover over the column in List Settings.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#3]

Shouldnt that leave me with a different "error" as in "Column not found" or such?

I also checked the settings and how the field is called. The names are correctly spelled, Sharepoint is set to German.

I just tried to e.g. =[Runtime]+[NoticePeriod] in a new column (also set to number) and it gives me an error saying "the formular kann not use columns. In order to define a colum, that is based on another column, use a 'calculated column'"

I am completly lost now...

[#4 · Accepted solution]

Hi @SOE_Tech 

I just tried it using that formula

DATUM(JAHR([Unterzeichnet]);MONAT([Unterzeichnet])+[Kuendigungsfrist]+[Laufzeit];TAG([Unterzeichnet]))

If you did create your site in german, then you need to use semicolons (";") instead of commas ('",") as parameter seperators in your formulas (like in german Excel)

But strangely it does not seem to matter whether you use the english or german names of the commands, so this also works:

Date(Year([Unterzeichnet]);Month([Unterzeichnet])+[Kuendigungsfrist]+[Laufzeit];Day([Unterzeichnet]))

Best Regards,
Sven

[#5]

now it works - i already attempted the ";" and swapped to "," as that at least gave me some error while other one left me stuck.

But thanks to both of you for helping me out!

Small Edit:
It seems you have to created a "calculated column" right from the start - i was not able to add the WORKING formular into an already existing column that was just a "date time column"

[#6]

I am now trying to make some "if then" additions - but the formular keeps showing up syntax errors.

e.g. if the runtime = 0 (no fixed runtime) i want no enddate calculated.

Could you help me out with that?

[#7]

Hi @SOE_Tech 

i would do that like this

WENN([Laufzeit]>0;DATUM(JAHR([Unterzeichnet]);MONAT([Unterzeichnet])+[Kuendigungsfrist]+[Laufzeit];TAG([Unterzeichnet]));"")

This will only calculate the date if you have a valid runtime greater than 0

Best Regards,
Sven
 

[#8]

@SvenSieverding 

I am really going nuts with these formulars -.- They feel extremly random and yours seem to work instantly.. i seem to not understand the syntax somehow..

I wanted to extend the formular a bit to figure out the correct "date of action" (e.g. i have to sign a new contract or give notice) with another formular that basically checks following:

Can you explain to me how I can create a "wenn(und" formular? I really want to understand the system so i dont have to constantly bother you 😕

[#9]

Hi @SOE_Tech ,

i know what you mean.. But i also get the "The formula contains a syntax error or is not supported." Message more than i like. I normally edit the formula in a text editor, split it up into multiple simple sub-formulas and test each one of them.... When they work, then i compose them into a larger formula

To connect to boolean statements via "AND" you use the "UND(<statement1>;<statement2>)" Function (or "AND"... This does not really matter, but the semicolon ";" as a seperator is important on a site with a german locale)

You did not paste your other formula, so for this example I'll be using '[Title]="Hello"'.

If it helps you and you edit the formula in a text editor then you can try to format the formula to make it more easy to understand. You can just copy&paste the formatted formula into the SharePoint Field.. It will still work with all the spaces (but it will be linearized again the next time you open the field settings)

WENN(
	UND(
		Title="Hello";
		Laufzeit>0
		);
	DATUM(
		JAHR([Unterzeichnet]);
		MONAT([Unterzeichnet])+[Kuendigungsfrist]+[Laufzeit];
		TAG([Unterzeichnet])
	);
	""
)

The lines 1 and 12 open and close the big "IF" clause.
Line 11 contains the "ELSE" value.... in our Case an empty String
Lines 2-5 are the condition
Lines 6-10 are the value that should be returned if the condition is true

If you have a site with a german locale and you see some formulas on the internet that you want to use, then you most likely have to exchange commas and semicolons with each other... Like in german Excel.

Best Regards,
Sven