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?