Weekly Newsletter Signup

• Ongoing Series

  • Stump the Panel: Q&A
  • The Calculated Column
    Dessie Lunsford
  • SharePoint Search
    Chris Quick
  • In Defense of SharePoint Designer
    Paul Galvin
  • The Road to Performance Management
    Paul Grenier
  • Video on Demand
    Mark Miller
  • Subscribe to the Weekly Newsletter
Search this Site



• Recently Written

  • EndUserSharePoint.com: Stump the Panel - SharePoint Q&A
  • EndUserSharePoint.com: Only 5 seats left for online SharePoint Dashboards Workshop
  • EndUserSharePoint.com: What’s up with the calculated column?
  • EndUserSharePoint.com: Extending Issues and Tasks - Part 3
  • EndUserSharePoint.com: Face Off with Sarah Palin
  • EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)
  • EndUserSharePoint.com: Can attachment file names be found through search?
  • EndUserSharePoint.com: Online workshop for creating simple dashboards
  • EndUserSharePoint.com: Case Studies in SharePoint Dashboards - Quote of the Day
  • EndUserSharePoint.com: Why would you do that?
  • The Weekly Newsletter  

• Display by Topic

  • Best Practices
  • Books
  • Calculated Column
  • Calendars
  • Chris Poteet
  • Chris Quick
  • Content Type
  • Dessie Lunsford
  • Document Management
  • External Links
  • General Observations
  • Guest Post
  • Information Architecture
  • Latest Projects
  • Libraries and Lists
  • Mark Miller
  • Navigation
  • Paul Culmsee
  • Paul Galvin
  • Paul Grenier
  • Question of the Day
  • Resources
  • Screencast
  • Search
  • Security
  • SharePoint 101
  • Site Administration
  • Tips and Tricks
  • Video On-Demand
  • Views
  • Web Parts
  • Weekly Newsletter
  • Workflow
  • Workshops

• Monthly Archives

  • October 2008
  • September 2008
  • August 2008
  • July 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008
  • December 2007
  • November 2007
  • October 2007
  • September 2007

• SharePoint Resources

  • Bob Mixon
  • Get the Point
  • Joining Dots
  • Paul Galvin
  • Sahil Malik
  • SharePoint Application Development
  • SharePoint Blogs at SharePointU
  • SharePoint Forums at SharePointU
  • SharePoint Screencasts
  • SharePointPedia

• Links

  • The Weekly Newsletter  
  • Log in

Dessie Lunsford
Points-of-Sharing

If you haven’t read through the previous article in this series and wish to follow along in the walkthrough, I encourage you to read it first, especially since we’ll be working with the Excel spreadsheet we created last time.

In the previous article on “Customizing a Task List“, we created a “Date Completed” column using a reference to “Today” in its formula. In order to accomplish this and not get one of the dreaded error messages that SharePoint likes to confuse it’s users with, we created a temporary “Today” column which allowed us to build the formula and run it. Afterwards, we simply deleted the temporary column to “finalize” our calculation.

One thing to note when using the idea of this “temporary” column is that whenever there is a need to go back in and edit the formula that uses “Today”, we will always have to re-create the temporary column first. This is needed for the exact same reason we created it in the first place - to fool the system into letting us use the name as a reference, and to bypass any of the errors that SharePoint will throw because of it. Thanks to Adam Davidson for pointing this out in a comment from last time. I had intended to mention this in the previous article, but wound up leaving it out due to the length of the post.

Picking up where we left off, the next column we’re going to create is our “How many days left to complete?” column. To shorten this a bit for the name of the column, we’ll just be using “Num of Days Left“. This column is designed to display a simple count of how many days we either have left, or are overdue - a positive number if “day’s left”, negative number if “days overdue”.

Open up your Excel spreadsheet from last time and enter in the following formula for the new column:

Formula:
=IF(B4=”Completed”,”",B7-TODAY())

This is a relatively simple formula in that our “IF” statement is simply looking to see if the “Status” field is equal to “Completed”. If it is, nothing will be displayed. If it’s not, then we take today’s date and subtract it from the “Due Date” and display the difference (this is where we get the positive/negative number possibilities).

Also, and this is only for when we’re working in Excel, format the result cell (B10) to be a “Number” with no Decimal places (right-click in the cell > choose “Format Cells”).

This formula is very similar to our first calculated column formula (”Date Completed”), but adds in a date calculation to return the difference between two dates.

Looking at the details of this formula, we see the following parts:

=IF(B4=”Completed”,”",B7-TODAY())

This literally means: “If the value of cell B4 equals the text “Completed”, we’re not going to do anything, but if it does, we’re going to take the value of cell B7 (”Due Date”) and subtract Today’s date from it, then display the number of days (difference between the two) into our cell (B10)”.

So, in this formula, the three parts of the “IF” function are as follows:

1. The logical condition to check (”Does the value in cell B4 equal the text “Completed”?)
2. Value if “True” (Display nothing)
3. Value if “False” (Subtract Today’s date from the “Due Date” and display the number of days difference)

Test this by changing the value of cell B4 to “Completed” (nothing will be displayed in B10).

Test also with leaving B4 as “Not started” and modify the “Due Date” (B7) to be before today’s date (number displayed as negative letting us know how many days overdue).

Test again with the “Due Date” set past today’s date (number will be positive letting us know how many days we have left).

Test one final time with the “Due Date” set to today (a zero will be displayed).

In order to get this into SharePoint, we run through the same steps as last time:

In SharePoint, go in to the “Tasks” list and its settings page.

Since we’re again using a “Today” reference in our formula, we’ll need to first create our temporary “Today” column to allow us to use it in our formula.

Create a new column called “Today”, leaving the rest of its settings as default.

Create another new column called “Num of Days Left”, make it a “Calculated” type and paste in the formula from Excel. Before saving, replace “B4″ with “Status”, “B7″ with “[Due Date]“, and remove the parenthesis after “TODAY” ().

Also, before saving, notice the brackets [] around the “Due Date”. Whenever a space or special character is used in a column name, it has to be enclosed in brackets or it won’t be recognized as the correct name. The brackets tell the system that literally anything in between them is the name and to ignore characters that are normally used as part of an expression - e.g. “Pros & Cons” has to be written as “[Pros & Cons]” in the formula or it won’t be recognized as a column.

Formula in Excel:
=IF(B4=”Completed”,”",B7-TODAY())

Formula in SharePoint:
=IF(Status=”Completed”,”",[Due Date]-TODAY())

After you have the new calculated column, we need to go back and delete our temporary “Today” column. This column was simply a placeholder that fools the system into allowing us to use the name for our formula. Deleting it will now match it up with the real “Today” data in the system, but without throwing an error.

To test the new column, just as we did last time, create a new item in the list with a “Start Date” of the (default) current date, and a “Due Date” of tomorrow.

Once saved, we can see immediately that our new column has a number letting us know how many days left we have to complete the task:

To test the various values that could be displayed, we’ll just edit the task and see what the results are.

Set the task as “Completed” (note that nothing is displayed in the new column).

Adjust the “Start Date” and “Due Date” back several days (note how the value will display as a negative number since we’re overdue).

Ok, so now we have a way to see the date a task was completed, and if it hasn’t yet been, then a way to show how many days we have left, or are overdue. This works, but probably isn’t the most user-friendly way to display the numbers since once we’re overdue the number displays as a negative.

This leads us to a modification, or “advanced” version of the “Num of Days Left” column we’ll call the “Days Overdue” column. This column is an expansion of the “Num of Days Left” column that performs the same function, but includes additional logic to make the results more meaningful and displayed in a “user-friendly” format.

The formula begins the same with an initial check to see what the text is in the “Status” field, but immediately you’ll notice it is very different (see the multiple “IF” functions?).

Formula:
=IF(B4=”Completed”,”Task Completed”,IF(TODAY()<B7,B7-TODAY()&” Days Left”,IF(B7<TODAY(),TODAY()-B7&” Days Overdue”,”")))

In this formula, we’re again running a check against the “Status” column to see what its value is.

• If it’s equal to “Completed”, we display a result of “Task Completed”.
• If its not “Completed”, we then run through two additional checks to see if we still have any days left to complete the task or if we’re currently overdue.
• If we have days left, we subtract “Today” from the “Due Date” and display the number of days left with the addition of some simple text letting us know that we have “x Days Left”.
• If we’re currently overdue, we subtract the “Due Date” from “Today” (notice the swap in position of the two columns in the calculation?) and display the value as “x Days Overdue”.

This form of calculation is known as an “IF-ELSE” chain, and can viewed as the following:

IF (condition to check)
{Do something if above is “true”, if false, do nothing and move on}
Else IF (condition to check)
{Do something if above is “true”, if false, do nothing and move on}
Else IF (condition to check)
{Do something if above is “true”, if false, do nothing and move on}
Else
{Do Something - think of this as the “cleanup” or “catch-all” - “if none of the above, do this”}

In the processing of the formula, the first check that results in a value of “True” will terminate the remaining checks. So, in our example, if the value of the “Status” field is “Completed”, no further checks are needed (neither of the remaining date comparisons happen). If the value is not “Completed”, it moves to the next comparison and attempts to reach a value of “True”. If “True”, it then processes the result of “x Days Left”, if “False”, it moves on to the last comparison. If the final comparison is also “False”, it will move on to the end and simply display nothing.

Since this is a more complicated formula, lets again examine each piece to get a more detailed view of how this type of formula is structured:

We’re using “IF” statements again, so we know we have three parts to each “IF”:

1. The logical condition to check
2. Value if “True”
3. Value if “False”

In order to achieve the chaining of “IF” statements, we will be substituting an “IF” in place of the “False” of the preceding “IF” statement in order to produce a result (stay with me).

So, to break this chain-of-checks down; let’s look at it literally like this:

“If the value of the Status field equals “Completed”, display the text “Task Completed” in the “Days Overdue” column. If it does not, is today’s date less than the due date? If so, subtract today’s date from the due date and display the number (difference between the two) in addition to the text “Days Left”. If not, is the due date less than today? If so, subtract the due date from today and display the number (difference between the two) along with the text “Days Overdue”. If not, don’t display anything.”

Since we’re using a nesting-type of approach to this (it’s not really “nesting” per say, but it does help to illustrate how the formula is structured), each of our “IF” statements three parts would look like this:

1. The logical condition to check (does the value of cell B4 equal “Completed”?)
2. Value if “True” (Display the text “Task Completed”)
3. Value if “False” (start another “IF”)
   1. The logical condition to check (is today’s date less than the “Due Date”?)
   2. Value if “True” (Subtract today’s date from the Due Date and display the number of days difference along with the text “Days Left”)
   3. Value if “False” (start another “IF”)
      1. The logical condition to check (is the Due Date less than today’s date?)
      2. Value if “True” (Subtract the Due Date from today’s date and display the number of days difference along with the text “Days Overdue”)
      3. Value if “False” (Don’t display anything)

Each “False” result starts another “IF” statement. Each “True” result displays a value, and the final result (if not “true” in any of the others) will display nothing.

In this case, if our original “IF” statement’s check returns a value of “False”, it hands off the processing of the “False” result to another “IF” (the “ELSE-IF”) which in turn does the same if it returns its own “False” for its check. Once each sub-process completes its task, it returns its result back to its parent until it reaches the “top” parent (original process) where it can display the final processed value for its “False” result.

Here are a couple of diagrams to illustrate this for those that like the “graphical” approach:

Standard “Nassi-Shneiderman” diagram used to “visually” walk through programming logic steps.

The end result of all of this is that if the original condition if “False”, it runs through two more checks until it reaches a final result, and then displays the value of that result.

To convert this into a compatible SharePoint formula we do the following in our “Tasks” list:

Once again, since we’re using a “Today” reference, we first create our temporary “Today” column to fool the system into letting us use the name.

Create a new column called “Today” leaving the rest of its settings as default.

Create another new column called “Days Overdue”, make it a “Calculated” type and paste in our formula from Excel. Before saving, replace “B4″ with “Status”, each “B7″ with “[Due Date]” (noting the use of brackets), and remove the parenthesis after each occurrence of “TODAY” ().

Excel Formula:
=IF(B4=”Completed”,”Task Completed”,IF(TODAY()<B7,B7-TODAY()&” Days Left”,IF(B7<TODAY(),TODAY()-B7&” Days Overdue”,”")))

SharePoint Formula:
=IF(Status=”Completed”,”Task Completed”,IF(Today<[Due Date],[Due Date]-Today&” Days Left”,IF([Due Date]<Today,Today-[Due Date]&” Days Overdue”,”")))

To test this new column, create a new item on the task list with a (default) “Start Date” of today and a “Due Date” of tomorrow.

On saving, we’ll now see:

Notice how it displays the remaining days in a more meaningful manner (”1 Days Left”)?

To test the various results that would display in each situation, we modify the task details.

Set the task as “Completed”. It will display “Task Completed” instead of a number.

Set the task back to “Not Started” and modify the “Start Date” and “Due Date” to several days back.

Notice how it now displays the day’s overdue amount without any negative numbers?

The main thing is that what we’re attempting to accomplish is to add in additional functionality to a default list that is pretty plain in its OOTB state. The first column we created in this article will do that exact thing, but doesn’t necessarily display information in the best format, so with the addition of a few extra lines in our formula that creates additional logic, we wind up with a second column (most likely to replace the first) that not only takes in account for each possible situation, but formats the results in a more user-friendly manner (which we’re all about - making it easier on the “End User“).

Hopefully what I’ve discussed here does make sense so far, but if not, don’t worry as we’ll be discussing these ideas further as we continue on in future articles.

Next time we’ll be adding in our two remaining columns (”Completed Early?” and “Overdue”) and introducing the concept of the “AND” into our “IF” which allows us to check for two conditions at the same time.

Till next time…
- Dessie

Dessie Lunsford
Points-of-Sharing

Suggestions for future posts on calculated columns are always welcome, and in fact are encouraged.

Some of the best scenarios to illustrate are the “real-world” problems that we each face day to day, so if you have an example, an idea you want to explore, or a “Can this be done with a Calculated Column?” question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.

Comments