I am a librarian, NOT a developer. But today I built a very cool formula that examines list metadata fields and dynamically applies formulas to calculate project milestone completion dates. Here’s my business scenario:

I have a SharePoint list that is used to track project tasks for my documentation department. The list has a variety of fields, including:

• Project name
• Project manager
• Task type – a Choice field with the following valid values:
  • Design documentation
  • Write documentation
  • Proofread
• Task description
• Technical writer assigned
• Task due date
• Task start date (this is the field I want to auto-calculate)

Project managers will come to this list to enter documentation tasks that need to be completed for upcoming projects. Many of the tasks may be logged early on in the project, but may not need to be started (or completed) until later in the project’s lifecycle. To help ensure that each task is kicked off in a timely manner, the project managers want to automate the calculation of task start dates. When the project managers log each task, they’ll identify the task type and the task due date. They then want SharePoint to look at the task type, deduce how much lead time will be needed to complete the task and automatically specify a task start date.

I’ve built this type of solution before, but have always used custom SharePoint Designer workflows to make my calculations for me. This wasn’t an option in this case, as the project managers wanted to avoid any SharePoint Designer customizations. And while I knew that Calculated columns offer a huge amount of functionality, I was unsure how I (the librarian) was going to figure out how to create the necessary formulas.

I started out reviewing Calculated column formula help guides, including http://msdn.microsoft.com/en-us/library/bb862071.aspx. From there I started experimenting with a basic formula that would look at the Task type field. When it found a list item whose Task type field was set to Design documentation, it would take the value in the Task due date field, subtract 52 days and report the results. Here’s this starter formula:

=IF([Task type]=”Design documentation”,[Task due date]-52)

And here’s how my calculated Task start date column looks, now that I’ve inserted this formula:

Let’s check out how this new field works. When you look at my list below, note that the top list item has a Task type of Design documentation. And just as we wanted, this task’s start date is automatically set to 52 days prior to the recorded task due date. Perfect!

There is a problem, however. All of my list rows that have a Task type not equal to Design documentation have a task start date value of 0. This is clearly not what we were hoping for. So I went back to the drawing board to create a formula that would work for binary fields (aka fields that have 2 possible valid values). Here’s the result:

=IF([Task type]=”Design documentation”,[Task due date]-52,[Task due date]-10)

Notice that this field includes 2 calculations–one for subtracting 52 days and another for subtracting 10 days. This formula will examine each list item’s Task type field. If it finds a value of Design documentation, it will subtract 52 days from the Task due date. If it finds any other value in the Task type field, it will subtract 10 days from the Task due date.

This is a bit better than our first option, but it won’t scale if our field has more than two options. I also don’t like how non-specific this formula is. It treats every value other than Design documentation identically.

After some trial and error, I evolved the formula again, this time modifying it so I could explicitly call out each possible field value and the corresponding date calculation to be made:

=IF([Task type]=”Design documentation”,[Task due date]-52,IF([Task type]=”Write documentation”,[Task due date]-40,IF([Task type]=”Proofread”,[Task due date]-10))

This new formula has 3 clauses, each of which specify a Task type value and the corresponding number of days that should be subtracted from the Task due date to determine the Task start date. Here are each of the clauses, broken out into different lines for easier viewing:

• IF([Task type]=”Design documentation”,[Task due date]-52,
• IF([Task type]=”Write documentation”,[Task due date]-40,
• IF([Task type]=”Proofread”,[Task due date]-10))

Here’s how my calculated Task start date column looks, now that I’ve inserted this formula:

And here’s the final result:

Nice! Now my Task start date field automatically reads the type of project task and determines the date when the task should be started. I should also mention that this solution works for both SharePoint 2010 and MOSS 2007.