SharePoint 2010 calculated list column

Question

=IF([Month (Calculated)]="1", "January","No date")
=IF([Month (Calculated)]="2", "February","No date")
=IF([Month (Calculated)]="3", "March","No date")
=IF([Month (Calculated)]="4", "April","No date")
=IF([Month (Calculated)]="5", "May","No date")
=IF([Month (Calculated)]="6", "June","No date")
=IF([Month (Calculated)]="7", "July","No date")
=IF([Month (Calculated)]="8", "August","No date")
=IF([Month (Calculated)]="9", "September","No date")
=IF([Month (Calculated)]="10", "October","No date")
=IF([Month (Calculated)]="11", "November","No date")
=IF([Month (Calculated)]="12", "December","No date")

Hi Everyone, Please help, I have a calculated column which reads data from a column that contains numbers like 1, 2, 3, 4 etc..I want the calculated column to check the current number and then assign a string.

Can anyone help with this nested if? Thank you

Thriggle Thriggle · Accepted Answer · 2017-03-02T19:19:44

You just have to nest the conditions in the "false" section of each IF() block.

For a simple example, imagine if you only had two months. Conceptually, your formula might look like this:

IF([Month (Calculated)]="11", // if the column equals 11...
    "November",  // then display "November"
    IF([Month (Calculated)]="12",  // otherwise... if the column equals 12
        "December", // then display "December"
        "No date" // otherwise... display "No date"
     )
)

Which translates to the following single line formula:

IF([Month (Calculated)]="11","November",IF([Month (Calculated)]="12", "December","No date"))

Now just extend that approach to all twelve months and you'll get a formula like this:

IF([Month (Calculated)]="1", "January", IF([Month (Calculated)]="2", "February", IF([Month (Calculated)]="3", "March", IF([Month (Calculated)]="4", "April", IF([Month (Calculated)]="5", "May", IF([Month (Calculated)]="6", "June", IF([Month (Calculated)]="7", "July", IF([Month (Calculated)]="8", "August", IF([Month (Calculated)]="9", "September", IF([Month (Calculated)]="10", "October", IF([Month (Calculated)]="11", "November", IF([Month (Calculated)]="12", "December", "No date"))))))))))))

Unfortunately, that formula exceeds the 255 character limit for a calculated column formula. To get around that limitation, you can use additional calculated columns to break out the formula into smaller pieces.

An example of how you might break that out is as follows:

Calculated Column 1:

IF([Month (Calculated)]="7","July",IF([Month (Calculated)]="8","August",IF([Month (Calculated)]="9","September",IF([Month (Calculated)]="10","October",IF([Month (Calculated)]="11","November",IF([Month (Calculated)]="12", "December",[Calculated Column 2]))))))

(Notice the reference to [Calculated Column 2] at the end.)

Calculated Column 2:

IF([Month (Calculated)]="1","January",IF([Month (Calculated)]="2","February",IF([Month (Calculated)]="3","March",IF([Month (Calculated)]="4","April",IF([Month (Calculated)]="5","May","No date")))))

SharePoint 2010 calculated list column

2 Answers