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?

  • Your column name is "NextMileStone" whereas in formula you have referred as "Next MileStone". Please correct the question for correct column name. – Monica Jagani Sep 14 '17 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 Sep 14 '17 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 Sep 14 '17 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 Sep 14 '17 at 7:44
0

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

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.

  • It (apparently) does work with string comparison though, because my formula works after I replaced the commas with semi-colons. – Justaguy93 Sep 15 '17 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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