Forum Discussion

LucasPaiva's avatar
LucasPaiva
Copper Contributor
Feb 15, 2024

Calculated Columns with if and choice

Hello!

I have a list with a choices column. I want to create a column with calculated data, which returns a calculated date based on the column of choice, adding 150 or 90 days to another date column.

I thought about doing something like this, but it didn't work:

=if([ChoiceColumn]="Choice1",[DateColumn]+150,[DateClumn+90)

How to fix?
microsoft lists

2 Replies

Sort By
  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Feb 15, 2024

    LucasPaiva 

     

    One correction related to the response by NikolinoDE, SharePoint calculated formula works with the display name of SharePoint list columns and not with the internal names.

     

    Also, note: 

    1. Sometimes comma( , ) does not work in formula (it is based on language or regional settings of your SharePoint site). So in that case use semicolon( ; ) instead of comma( , ).
    2. Use correct display name of your SharePoint columns in formula.
    3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Feb 15, 2024

    LucasPaiva 

    To achieve the desired functionality in a calculated column in SharePoint or Microsoft Lists, you can use the following formula:

    =IF( [ChoiceColumn] = "Choice1", [DateColumn] + 150, [DateColumn] + 90 )

    Here's a breakdown of the formula:

    • IF: This function allows you to evaluate a condition and return one value if the condition is true, and another value if the condition is false.
    • [ChoiceColumn] = "Choice1": This condition checks if the value in the [ChoiceColumn] column is equal to "Choice1".
    • [DateColumn] + 150: If the condition is true (i.e., if [ChoiceColumn] equals "Choice1"), this part of the formula adds 150 days to the value in [DateColumn].
    • [DateColumn] + 90: If the condition is false (i.e., if [ChoiceColumn] does not equal "Choice1"), this part of the formula adds 90 days to the value in [DateColumn].

    Make sure to replace [ChoiceColumn] and [DateColumn] with the actual internal names of your choice column and date column, respectively. Also, ensure that the choices in your choice column match exactly what you've specified in the formula (e.g., "Choice1").

    Once you enter this formula into the calculated column formula field, SharePoint or Microsoft Lists will automatically calculate the dates based on the conditions specified. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources