Forum Discussion

SOE_Tech's avatar
SOE_Tech
Brass Contributor
Feb 01, 2023
Solved

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's avatar
    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



RobElliott's avatar
RobElliott
Silver Contributor
Feb 01, 2023

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)

SOE_Tech's avatar
SOE_Tech
Brass Contributor
Feb 02, 2023
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...
  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    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's avatar
      SOE_Tech
      Brass Contributor
      Feb 14, 2023
      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?
      • SvenSieverding's avatar
        SvenSieverding
        Bronze Contributor
        Feb 14, 2023

        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
         

    • SOE_Tech's avatar
      SOE_Tech
      Brass Contributor
      Feb 02, 2023

      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"

Resources