Category Archives: Calculated Columns

Trick or treat? Group items by month

Posted on by

OrderedMonthsIt’s that time of the year again when the unnatural becomes the norm, so let’s continue the tradition started last year with the SPELL program. Our goal today will be to display list or library items grouped by month, as shown in the screenshot.

The deal is that we don’t want any custom code or workflow here, just the regular out of the box UI features. We’ll be creating two calculated columns, called Year and Month, where we’ll insert the appropriate formulas. I chose the “Modified” column for my sample formulas, but of course any other date column would work too.

The YEAR and MONTH functions

YearNumber

A quick review of the SharePoint date functions gives you YEAR and MONTH that should fit the bill:

Year:  =YEAR(Modified)
Month: =MONTH(Modified)

The result is less than satisfactory though (see screenshot), as the year is displayed with a thousands separator. and months are displayed as numbers.

The magical TEXT function

MixedMonthsFurther exploration will take you to the TEXT function. It is not very well documented in SharePoint, fortunately you can rely on the Excel documentation and come out with the following formulas:
Year:  =TEXT(Modified,"yyyy")
Month: =TEXT(Modified,"mmmm")

Still not happy with the result? Right, the months are displayed in alphabetical order, not sequential order, not yet an ideal experience for our end user.

So let’s pull our last trick, and use the following formula for the calculated month:
Month: =REPT(" ",13-MONTH(Modified))&TEXT(Modified,"mmmm")

You can see the final result live on this page.

What’s the trick? We are still relying on the out of the box alphabetical sorting, but to force the order, we are adding a bunch of white spaces before the month name. The calculated Month actually contains the following values (each _ represents a white space):
____________January
___________February
__________March
…
__November
_December

Now, why don’t we see these spaces on the Web page? What makes the magic work is that when you insert multiple spaces in a Web page, the html specification says that

user agents should collapse input white space sequences

That’s it!

If you want to get really fancy, you could even use the zero-width space character. The best part is that people who edit your formula won’t even understand the trick, as the zero-width space won’t be visible (there’s however a good chance that they break your cool formula).

To take this further

You can apply this trick to other situations. A typical example is a color code. The alphabetical order will give you Green-Red-Yellow or Amber-Green-Red, you can address that by adding the appropriate leading spaces.

Be careful with this technique though: even if the rendering looks fine, the spaces are indeed stored in the field, and this might break other customizations. So this trick is better kept in a calculated column that will be exclusively used for rendering purposes.

HTML Calculated Column + Client-Side Rendering

Posted on by

Five years after the first release, the HTML Calculated Column remains the most popular topic on this blog.

The original page has been visited more than 200,000 times. It is definitely outdated, and in recent years I have pushed several new variations of this technique. The most popular is the color coding solution posted in the SharePoint User Toolkit, backed by this tutorial.

The most frequent issue reported by users has been the upgrade from SharePoint 2007 to SharePoint 2010. This is actually all taken care of in the above links… but you need to read the fine print.

I plan to rewrite the instructions, especially as in the meantime Microsoft has pushed another version of SharePoint, and the SP 2010 update… doesn’t work with SP 2013! Well, there’s actually a simple fix for SP 2013, and “Panoone” posted it as a comment a couple days ago (@Panoone: thanks again! And let’s get in touch to discuss this further).

But that’s not all. SP 2013 brings a bunch of new client side technologies, and one of them works very well in our case: Client-Side Rendering.

What is Client-Side Rendering?

JSLinkEditYou can use Client-Side Rendering (CSR) in SharePoint to manipulate the rendering process of list views. Does this sound familiar? That’s exactly what my HTML Calculated Column has been doing for years! Except that now it is an official component integrated with list views. When you edit a Web Part, the very last option is the JS Link placeholder (see screenshot).

It will certainly take several weeks before I find time to update the SharePoint User Toolkit, so for those of you who are already familiar with both CSR and the HTML Calculated Column, let me share the code for SP 2013.

In the code, Calculated is the name of the calculated column.

var ccContext = {
  Templates: {
    Fields: {
      "Calculated": {"View": "<#=STSHtmlDecode(ctx.CurrentItem.Calculated)#>"}
    }
  }
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(ccContext);

So easy, just 200 characters! STSHtmlDecode() is a JavaScript function provided by SharePoint that allows the conversion of the html string.

Of course, you could avoid using a calculated column, and build the html directly in the JS Link file. But then each rendering would require a different file. The beauty of the HTML Calculated Column is that one single JS Link file can support all the views in your site.

A warning!

Some time ago, I wrote about security risks when you use the HTML Calculated Column. As far as I can tell, the same warning applies to Client-Side Rendering. Handle with care!

CSR references

When Microsoft released SharePoint 2013, the documentation on Client-Side Rendering was quite poor. Fortunately SharePoint bloggers stepped in and you can find some nice posts to get familiar with CSR. Wes Preston’s blog is an excellent start.

So is the old way dead in SharePoint 2013?

Not quite yet! Not every view accepts Client-Side Rendering, and for example you’ll still find my tutorial helpful for calendar views.

Discussion: How do I exclude weekends when counting the number of days between a start and end time?

Posted on by

The only purpose of this post is to relay a discussion that has been active for almost two years on LinkedIn, in the SharePoint User Managed Solutions group:

How do I exclude weekends when counting the number of days between a start and end time?

You would think that such a question would bring a straightforward answer. But apparently it’s not the case, and depending on their site settings people get different results.

Feel free to try out the suggestions in the discussion, and let the group know which one(s) worked for you!

The SharePoint User Managed Solutions group on LinkedIn currently has ~600 members. It’s purpose is to discuss customizations done from the user’s side (Calculated Columns, SharePoint Designer, Web services, client-side scripts, Office 365, etc.).

 

 

Interactive demo: charting in SharePoint

Posted on by

Last month I published a first demo based on the SPELL mini-BI solution, showcasing the ability to build multi-level drill-down charts.

Today we are taking a look at the edit mode, thanks to an interactive demo. I think the best way to understand what this is about is to simply follow the link, read the instructions on the page, and start playing with the charts. Then you’re welcome to post feedback and questions here, and I’ll try my best to respond.

The previous demo used the Google GeoChart API. This time, the pie and column charts rely on the Dojo Toolkit. Dojo offers a wide range of charts, as well as multiple themes (I only picked a handful for the live demo). It also comes with plugins like animations and tooltips (also featured in the demo).

The Matrix View is homemade, and part of SPELL.

Here again, the data comes from grouped views (3 to choose from for the demo). This time I have only set up a two level drill-down, and clicking on a chart element will directly open the SharePoint list.

I made a recent addition to the demo that I find interesting, I call it “client side calculated column”. It allows you to enter directly in the form a formula, similar to what can be done with calculated columns in SharePoint lists, just much simpler (currently only simple operations). In the demo, the “Value” field plays the role of the calculated column (see the context help for an example).

I won’t describe this advanced customization in detail on my blog, but I’ll be happy to elaborate on specific points. If you think it could address your business needs, you’re welcome to subscribe to the SPELL interest list and you’ll receive in a couple weeks a sample to test on your own site. And if you are looking for a free, entry-level charting tool, remember to check out the SharePoint User Toolkit!

It’s All About the Grades-Tracking Schedules,Assignments, and Grades on a School Site (Part III)

Posted on by

Trudy HutzlerGuest Author:  Trudy Hutzler

Welcome to the third installment of the series on my School Site and how we put it all together.  In this post we will walk through how we track the grades, because at the end of the day all it is all about the grades.  For tracking grades, the school interface was often inaccurate with some classes included assignments not yet due as zeros until an actual grade was entered; others would be late but not counted until an actual 0% was entered, and so on.  We needed a way to track and calculate her grades so we could monitor her progress.  Not just tracking the grade for the individual assignments, but for the class average, the grades for the grading period and the year overall.  Finally we wanted to make it easy for us, as parents, to get the update on her grades at a glance.Now before we go any further, my daughter has asked me to again state that no actual grades were used in the making of this demo.  All grades and classes have been changed to protect the innocent.

Home Page

The All Grades tab shows the grades for each class in one place.  Any assignment that is past its due date is grouped by Class and the assignment grades for each class are averaged.  Although, since some assignments are weighted heavier than others this is not entirely accurate it does give us an overall idea of how she is doing grade wise.

All Grades Tab on Home Page

All Grades Tab on Home Page

To see the class grade average and individual assignment grades, you know for deciding if she can go out with her friends, or take a mental health day, you click on the Class to expand the node.

The Math, English, History, PE, and Science tabs, on the home page, simply list out all the assignments for that class, for the current quarter.  When my daughter is working in a particular subject she can see everything that was assigned and work on it all at once to save time.

Science Tab on Home Page

Science Tab on the Home Page Displays Current Grades for this Class

I also added a Legend at the bottom of the page to help other users, like my Husband, to know what all the different colors and symbols mean.

Lists

All of the final assignment grades are recorded in the Assignment List.  The problem was we had decided we would only track one quarters worth of assignments at a time to keep the assignment lists from being an overwhelming mess of information.  So I needed a way to track the final grades for each class for each quarter, semester, and the year overall.  To do this I created a new list called Grades.

Grade List

Grades list shows all final grades for the year to date

I explained above that some grades are more heavily weighted than others and so the grade average was not exactly correct, but at some point needed to track the actual final grades.

I enter in her final scores from her report card, and the list calculates the rest averaging the two quarter grades to get the semester grade, and all quarters to get the year overall.  Now you won’t need to track grades in your workplace, but what about other performance indicators, percentage of tasks completed on time, or percentage of returned or defective parts?  You can use your own imagination on how something like this might work for you.

Finally to keep everyone in the loop on where my Daughters grades stand I post an abbreviated view of the grades on the home page for quick reference.

Image Rotator and Grade Summary

The image rotator and grade summary view from the homepage

Now did you notice the picture of the waterfall above the grades summary view for the homepage?  It is a picture of the Kent State University campus.  Kent is one of the colleges my daughter is considering. We also wanted the site to be as attractive in form, and it was useful in function so we added the image rotator solution to the home page.

I created a picture library to hold the pictures I wanted to show in the image rotator web part, and every 5 seconds the image changes to display the next picture in the library.  Now for this demo I used pictures of the Kent State campus, but my daughter can change these and show pictures of friends, cartoons or comics, whatever she wants.  This just gives the page a little visual appeal.  I have been known to place Calvin and Hobbes Snowmen comics in a rotator part during periods of time when we are having a lot of snow storms, or other holiday cartoons, and so on.  This helps to draw users to the page, and gets them clicking around and using the site.  So never underestimate the power of visual appeal.

Well now you have seen our School Site, and seen how we combined individual solutions to make a user-friendly, visually appealing solution.  From here we are going to roll up our sleeves and get down to the nitty-gritty of exactly how we created each part of the solution.

My hope is that something that I have done will help spark your imagination and start you thinking about ways that you can use these solutions in new and exciting ways.

A School Site to Track Schedules, Assignments and Grades (Part II)

Posted on by

Guest Author: Trudy Hutzler

Welcome to part two of the School Site Overview.

In the last article I gave you some background about why we created the School Site, and I showed how we tracked classes. In this article we start to get into more of the fun stuff as I walk you through how we track homework and assignments.

The Home Page

Again starting on the Home Page, where we have used Easy Tabs version 5 to organize our data, I want to start with the “All Assignments” tab. Here is a list of all assignments that are due for the current quarter and their status.

This shows whether an assignment is submitted, late and by how many days, or how many days until it is due. To evaluate how many days till due, or late against the due date I used Christophe’s new Countdown/Countup formulas.

The priority of the assignment is indicated by the font color of the Assignment Title. I used the Color Coded Calendar tool from the SharePoint Users Toolkit for this piece and just tweaked it a little for my use.

The progress % is a visual indicator showing the percentage of the assignment that has been completed. It moves from Red to bright green as it moves across the bar. The actual color changes in 10% increments.

With online classes, it is not unusual to have classes with over 100 students, so as you can imagine it sometimes takes the teachers a while before they grade all those assignments and post the grades. Until they are all graded the assignment remains blank or at 0% making it hard to know if the assignment was submitted and being graded or was late. So we added the Submitted field so we as parents know if the assignment was submitted, and my daughter can be sure she didn’t forget to turn in the assignment after she completed her work, which if you have teenagers you know can happen a lot.

The Due Today and Due This Week tabs are pretty self-explanatory they are filter views of the Assignments list which list all assignments that are due. The image below shows the Due This Week tab.

The Late Assignments tab is another filtered view which gives us a list of all assignments that are past their due date, and have not been marked as submitted.

The List

As I said in my last article the Lists are the real work horse of the School Site and the Assignments List is really the nerve center for the whole site, everything else is just for support of this one list. In this list all homework assignments for the current quarter are tracked, and the grades recorded. We even track how many days until it is due, or how many days an assignment is late. We also provide access to any links needed to complete the assignment, and scheduled time for completing the work. It all happens here, and it all gets integrated from here as well.

Keeping track of the assignments is only one part of process; you also need to manage your time so that you can actually get the work done and submitted.  But as we all know clicking around between your to-do list and your calendar is a pain, even for the most disciplined of us, but especially for a teenager.  I knew if it wasn’t easy to move between assignment list and calendar and somehow tie the two together it wouldn’t be long before my daughter gave up and went back to her old tried and failed methods of doing her work.   So we needed to find a way to connect her Assignments list with the Schedule calendar.  You may remember the Schedule calendar from the last article.

This calendar color codes the entries by changing the background color according to the category you choose.  If you look at the figure above you will see some of the entries have a green background, another entry has a red background, and so on.  When you create a calendar entry there is a Category field where you can choose a category like “Holidays-No School” which would give the entry a green background and “Report Periods” which would give the entry a red background.  The category you choose will determine the background color of the calendar entry.

But that’s not all, if you call in and order in the next 30 minutes you will also receive the added feature of the Assignment List integration.  You can link your calendar event with an item in your assignment list by using a hyperlink that when clicked will open up the view list item dialog box from the Assignments list.

 Let’s say you are checking out your schedule in the calendar and see an assignment you are supposed to be working on, but can’t remember what it is you were supposed to do for the assignment.  If you open the calendar event, it will look something like the figure below.   You can now scroll down to the Assignment field and click on the hyperlink, which for this example is titled “Assignment 4”.  

This will open the View list item dialog box for this homework assignment from the Assignments list, which you can see in the figure below.  The Assignment List item will even open in a new window so you don’t have to navigate back to the Calendar page again. 

But wait that’s still not all, did you notice the Assignment Page link at the top of the calendar page?

Click that link and go straight to the All Tasks view of the Assignments List, as shown in the figure below.

The All Task, as seen above, is the default view for this list. Notice that in this view we are using the Title field that is linked to the edit dialog box. This gives us quick access to edit the item and update its information. We can easily see how much progress has been made on the assignment by the progress bar. If you click on any of the links in the Scheduled column it will open the dialog box for the calendar item associated with this assignment.

So if you need to reschedule your time to work on the assignment or extend it to another day you can do so easily from the list item itself without having to navigate away from the page. But what if no work time has been scheduled for this item yet? No problem, to schedule time to work on this item, simply select the assignment in the list, hover your mouse over the title of the item then click on the down arrow to open the drop down list item menu. There we have added a new action called Schedule Work using SharePoint designer.

Click on Scheduled work and you will be redirected to the Schedule Calendar where you can schedule time to complete this assignment. Then simply hit the back button on your browser, or click on the Assignments Page link at the top of the page to return to the Assignment list. Finally notice the last column Late Status, this is our newest addition to the site and is based on the Countdown/Countup Formulas Christophe debuted in his blog not long ago. Once I saw them I just had to have them, and Christophe was kind enough to let me beta test them. I of course altered them just a tiny bit to better fit my purposes. The formula you are seeing in this view is the system time formula; I will show you the Internet Time formula in just a little bit. I have used both for demonstration purposes.

So basically that is the list, I have used views to change the way the list looks and functions based on what I needed to accomplish. For example, to track late assignment I created the Late Assignment view that you saw on the Late Assignment tab on the home page. Or I have created a calendar view to show when each assignment is due on a calendar. Some views have a few more visual indicators, like the Details view.

I wanted to be able to see which assignments were more heavily weighted and therefore, should have a higher priority, but I didn’t want to add another column when I was already struggling to keep all the information from running together in a hodge podge. So I made a dual purpose field that would display the Title of the assignment and then color code its priority level by changing the color of the font. May not be real fancy, but it is effective.

Remember when I told you we would be seeing the Internet Time based countdown/countup formula later, well here is in the IsLate column. If you need to create more visual impact this is the one for you, it combines the countdown/countup information with an icon image to really get your attention. I also tweaked this formula a tiny bit to make it more suitable for my needs, mainly by changing the text.

As I have said before replace classes with projects, assignments with milestones, and grades for progress or performance indicators and you can see how these same concepts can be adapted to the business world.

In my next post I will cover how we track grades, because in the end it’s all about the grades.

A School Site to Track Schedules, Assignments and Grades (Part I)

Posted on by

Guest Author: Trudy Hutzler

Trudy currently works as a Senior Technical Architect for AT&T Hosting and Application Management, where she provides Windows Server and SharePoint support and administration.  She is also a contributing author for the new Mastering SharePoint Foundation 2010 book.

As a SharePoint Administrator I often get asked about adding visualizations and such to SharePoint to add that little something extra to the out of the box SharePoint experience.  As an avid follower of Path to SharePoint I find many of the solutions Christophe has provided are a great way to enhance the users SharePoint experience without requiring me, as the Administrator, to maintain a lot of code or customizations on the server.  As good as the solutions are on their own, when you begin to combine them and layer them you can create something that is user friendly and visually appealing.

To demonstrate this I am going to share with you a School Site that I created for my oldest daughter who attends school online.  Now obviously not everyone will have a need for a School Site to track classes, homework assignments, and grades, but if you replace classes with departments or projects, homework assignments with milestones or requirements, and grades with assessments or performance ratings, you can begin to see where this concept can be applied to many more business related activities.

To make this easier to follow I am going to break this down into several posts, the first few will be an overview of the site and what it looks like, and then later posts will cover how I created each part and tied them together.  When it is all said and done I hope to provide readers with a copy of my site, and all the formulas I used.  So let me start by giving you the nickel tour.

BACKGROUND

The high schools in our area are not all that great, so my oldest daughter attends a state chartered online school.  We love the way the classes themselves are conducted, but the homework and grade interface is difficult to navigate, confusing, and it is very easy to miss an important assignment simply because it is hidden deep in layers of folders.  After an entire year of missing and late assignments and a constant struggle to navigate the interface, my daughter came to me last summer and asked me to help her create a SharePoint site on my development farm I have at home that she could use instead.

What you are about to see is the current version of that site.  My daughter would like me to note that all names, classes, and grades have been changed to protect the innocent, and that no actual grades have been used in creating this demo.

This site is created on the Team Site template, and I am using SharePoint Foundation 2010, however most of the functionality will work on SharePoint 2007, with a few exceptions like calendar overlay which is only available with SharePoint 2010.

As we go through the series I will refer you back to the blog entries I used to create each feature, and make all of the formulas available.  I will also be adding them to a special document library I have added to the demo site which, with Christophe’s help, I hope to make a copy of the site available to the readers for their use.

ON THE HOME PAGE

The site itself has three main functions; track classes, track homework assignments, and track grades. In this post I will be walking through the parts of the site that we use to track classes.

For tracking classes, my daughter needed to know the days and times her classes were scheduled for and what each teachers fax number was for submitting work she was unable to submit in the drop boxes provided by the school.  She also needed to be able to keep control of her schedule by scheduling in time to do her assignments. As parents we needed to know when she was supposed to be attending her online classes, the name of her teachers and how to contact them if we had questions. We also needed to know when the grade periods started and ended, and when there was no school.  Most of all we needed a way for busy parents, as well as the student, to be able to track it all at a glance.  This is what we came up with.


 
On the home page of the school site we used Easy Tabs to create an easy way to organize and navigate through the information we wanted “at our finger tips”. Some of the views are for Mom and Dad’s information, others are for our daughter’s use, but it is all there in one spot.

This first tab “Class Calendar”, as seen above, is a view of her class schedule, but to make it even more informative we over laid her schedule and assignment calendar views to give it a more complete view.

Next we will look at the tabs pertaining to classes and schedules.  The Schedule tab, This Week tab, and the Today’s Schedule tab show a calendar view of the scheduled classes and events for the month, the week, or for the day.  This helps other family members know when she is busy attending her web classes and can’t be disturbed.  This actually comes in very handy on days when my other children have no school, like snow days, and they know when they need to be quiet and leave their Sister alone, however you can use something like this to tack meetings, or deadlines, schedule events, etc.

For this calendar we have added color coding so we can easily differentiate between scheduled classes, work time, and holidays.

The last tab is Print Preview click on one of the other tabs that you would like a printed version for, then click on the Print Preview tab and it will create a printer friendly view of that tab, click on your browsers print button to print.  Once you have printed out your page if you look at the Print Preview tab it now reads Back to Page.  Click on this tab and you will be returned to your regular view.

For us, this gives my daughter a printed copy to keep with her and refer to throughout the day, or a schedule to post of the refrigerator, or just a list of assignments to follow up on at the end of the day, but you can also use this feature to print out pages to add to a report or share with others who may not have access to your site.

LIBRARIES

We are using the Shared Documents library to keep copies of any downloaded forms, assignments, test, or home work she has written up and scanned to email to the teacher or upload to the Drop Box in the class web site.  This way if something doesn’t reach the teacher the work is handy and can be easily resubmitted.
She also has to keep an activity diary for PE, she didn’t want those getting mixed in with other assignments so we made a separate library for it.
Finally for this demonstration I added a third document library to hold text file copies of all the formulas and calculations used in creating this site.
                                             

 

LISTS

It is in the lists that the real action starts.  It is the Class Calendar, Assignments, Schedule, Contacts and Grades that are the real work horses for this site.  Since this post is all about organizing and tracking classes we will look at the Class Calendar and Contacts lists.

The Class Calendar is really just the Team Site Calendar renamed.  We added all her regularly scheduled classes in as events, and later over laid the Assignment Calendar, which is how my Daughter schedules in time to actually work on her assignments, to increase its impact and usefulness.


 
Next we needed to track information about the classes, teachers and contact information so we created a contact list.  We started with a basic contact list then removed fields that weren’t needed and were just cluttering up the place, and added a few custom fields till we got what we needed.


 
We kept the usual contact fields like First and Last Name, Phone Numbers, and such.  Then we edited the Category from your typical Work, Meeting, Phone Call, etc. to something more school oriented like Teacher, Advisor, School Staff, Student and Other.  Finally we added another Choice field and added in the name of her classes.

At this point my Daughter decided she needed a place to keep track of all her other dates and to schedule time to actual work on each assignment.  So we created another calendar called Schedule which, since it would track multiple types of events, we made into a color coded calendar.


 
This calendar color codes the entries by category.  When you create a calendar entry, along with all the usual fields like title, location, start and end time, all day event or reoccurring event check boxes you also have a category choice field.  The category you choose will determine the color of the calendar entry.


 
So now you can see how using calendar overlays, and color coded calendars, in conjunction with Easy Tabs can begin to create a very useful interface.  This same concept can easily be adapted to tracking projects, deadlines, meetings and other deliverables.  And you don’t have to be a code ninja to get the job done.

In my next post I will get into the real nitty gritty of what makes the School Site work when I show you how we are tracking, and scheduling homework and assignments.

Maximum number of IF statements in a Calculated Column?

Posted on by

If you said seven, or eight, you spoke too fast…

I just answered a question on the Stump the Panel forum, and thought others could be interested.

The context

I recently published a new version of my tool for color coding calendars and lists. This tool generates a formula that associates each item of a choice list with a color:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))

For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.

The issue

In practice, users often have more than 7 items in their choice list. For 8 choices, the formula becomes:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,IF([Status]=”Choice8″,”Yellow”,””)))))))))

But if you try it, here is the message SharePoint throws back at you:
The formula contains a syntax error or is not supported.

So here is the sad truth: you cannot have more than seven nested IF statements in a calculated column.

When users discover this – by trial and error, via a forum, or from their experience with Excel – the next step for them is usually to try and break their formula across multiple columns, each containing a maximum of seven nested IFs. However, in most cases there is a simpler way.

The workaround

We have seen that a calculated column doesn’t allow more than seven nested IFs. But what if they are not nested? Let’s give it a try with my formula generator:
– First pass: choice1 to choice7
=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))
– Second pass: choice 8 to choice 14
=IF([Status]=””,”Black”,IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,””))))))))

Now, I just have to concatenate these two using the & symbol:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))&IF([Status]=””,”Black”,IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,””))))))))

There is still something wrong as the “” (no selection) option appears twice. So I need to remove the redundant one (in orange above, don’t forget the last parenthesis) to get my final result:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))&IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,””)))))))

And voila, a formula that fits in one calculated column, with 15 IF functions!

“That’s cool, Christophe! So… when can we see this in your formula generator?”
Ok, I’ll update it… but seriously, is it reasonable to use more than 7 colors for color coding?

Other options

For the record, IF is not the only SharePoint function allowing to build conditional formulas. See for example in this post how I used the CHOOSE function. And yes, there will be another formula generator built on CHOOSE!