0

I have a question regarding Calculated Columns.

I have a Column Called "NextMileStone", this column can contain "PM010", "PM020, "PM040", "PM070", or some other choices that would be the "else" choice in my if Statement. Then I have a column for each of the previously mentioned "PMs", these contain a date.

Now I want to create a calculated Column, called "Date Next Milestone", that should ideally fetch the date from the "PM" columns, based on which PM is set as the Next Milestone in "NextMileStone".

I created the following formula to do this: 

"=IF([NextMilestone]="PM010",[PM010 Date],IF([NextMilestone]="PM020",[PM020 Date],IF([NextMilestone]="PM040",[PM040 Date],IF([NextMilestone]="PM070",[PM070 Date],DATE(2000,1,1)))))"

However, sharepoint always gives me a syntax Error. (Disregard the "else" choice that I set to first of January 2000 for now, I tried different values and I don't think the error lies in the else choice)

I suspect that this happens because I cannot reference the value of a choice field as a string. My question is, how do I reference the value of a choice field in a formula?

Improve this question
4
  • Your column name is "NextMileStone" whereas in formula you have referred as "Next MileStone". Please correct the question for correct column name.
    – Monica Jagani
    Commented Sep 14, 2017 at 6:55
  • I have fixed it, thank you for your comment. I have filled in the name of the column via the function to directly add in columns in the sharepoint Calculated Column Creation mask, so that was (unfortunately) not the problem, I still edited the question to make it more clear.
    – Justaguy93
    Commented Sep 14, 2017 at 7:06
  • in the formula, put only column value "[NextMilestone]" and see the output. Choice column value does work with calculated columns
    – Monica Jagani
    Commented Sep 14, 2017 at 7:22
  • Well then maybe it doesn't work in IF statements? Because I am basically comparing a choice to a string in my IF statement, right?
    – Justaguy93
    Commented Sep 14, 2017 at 7:44

3 Answers 3

Reset to default
0

I am swiss, and in Switzerland, you need to use ";" instead of ",", because the comma is reserved for numbers...

Improve this answer
0

Choice and Lookup Columns save the values not in plain text, think of it as an array. You need to compare the IDs of the items.

Improve this answer
1
  • It (apparently) does work with string comparison though, because my formula works after I replaced the commas with semi-colons.
    – Justaguy93
    Commented Sep 15, 2017 at 12:52
0

I think you should change the position of the "" and it should be like this instead:

=IF([NextMilestone]=PM010,"PM010 Date").

You can also check reference:

https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.