Sharepoint 365 List - adding date

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" ...

Document Library

SharePoint Online

Usage

SvenSieverding
Feb 02, 2023
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

SOE_Tech

Brass Contributor

Feb 16, 2023

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 😕

SvenSieverding

Bronze Contributor

Feb 17, 2023

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

Forum Discussion

Sharepoint 365 List - adding date

Resources