Dessie Lunsford
Points-of-Sharing

If you haven’t read through the previous three articles in this series (Part I, Part II, Part III), I would encourage you to read them first as we’ll be continuing on with the use of an Excel spreadsheet developed in each of them.

 

 

Review and Next Steps

In the last article, we created a calculated column which gave us the ability to see if a task had been completed early. This type of information could be beneficial to managers performing an audit of a particular users (or groups) performance in completing items in a timely manner.

This time, we’ll be completing our customization of the “Tasks” list by adding in our final column called “Overdue?”.

The Formula

The “Overdue?” column is used to see “At-a-glance” which tasks are currently overdue and display a simple “Yes” or “No” depending on its status (which could assist in the day-to-day management and processing of Tasks).

Compared to previous examples, this column is rather straight-forward in its formula and what it’s looking for, but at the same time it can be somewhat confusing unless you understand the structure and logic of the formula and how it works to produce a specific result.

(See the previous articles in this series to create and setup your Excel spreadsheet.)

Formula in Excel:
=IF(AND(B7<TODAY(),B4 <> “Completed”),”Yes”,”No”)

Once again we see our old friend, the “IF” statement, as well as the “AND” function we discussed last time.

Dissecting this formula, we begin with the “IF” and its three parts:

1. The logical condition to check (is the “Due Date” less than today’s date? AND is the value of the “Status” column not equal to “Completed”?)
2. Value if “True”(display the text “Yes”)
3. Value if “False” (display the text “No”)

In the formula’s conditions, our first check is looking to see if the “Due Date” is less than “Today’s” date. If it is, the result of the first check is “True”. This is “technically” ok by itself, but it doesn’t deal with tasks that haven’t been completed yet, so we need to combine this with a second check to see what the “Status” is of the task.

For the second check, instead of looking to see if a specific value exists, we’re looking to see if it doesn’t. In this case, if the “Status” field does not equal “Completed” then the result is “True” (anything else will yield a result of “False”). Again, by itself this doesn’t really help us because not every task that hasn’t been completed is overdue (it wont be overdue until we have moved past the actual due date).

Combining multiple checks together (up to 30 of them) is a form of what’s known as “Boolean Logic“. To accomplish our end result of either “True” or “False” for the combination of these checks, we’re going to use an “AND” statement to merge our two checks into one statement that must be “True” (as a whole) in order to produce a result of “Yes” being displayed (note – the “AND” by itself will only produce a “TRUE / FALSE” result, so by adding it to an “IF”, we can then control the displayed output of “TRUE” being displayed as “Yes” and “FALSE” being displayed as “No”).

If we use another type of “Logic Diagram” tool called a “Truth Table“, we can check each combination of values to see just what results are possible.

Input1 (1st check)	Input2 (2nd check)	Result of each	End Result
Due Date is less than today	Status not equal to “Completed”	TT	TRUE
Due Date is not less than today	Status not equal to “Completed”	FT	FALSE
Due Date is less than today	Status is equal to “Completed”	TF	FALSE
Due Date is not less than today	Status is equal to “Completed”	FF	FALSE

 

The “Truth Table” although depicted rather simply here, can be used in conjunction with advanced calculations as well. In future posts, I’ll dive deeper into the use of this tool to aid in checking your formula logic.

In our formula (and following the examples in the Truth Table):

1. If the “Due Date” is less than “Today” (meaning we’re currently past the “Due Date”) and the “Status” field does not equal “Completed”, the entire comparison is “True” which results in a “Yes” being displayed (True: True).
2. If the “Due Date” is not less than “Today” (meaning we still have time left) and the “Status” field does not equal “Completed”, the entire comparison is “False” which results in a “No” being displayed (False: True).
3. If the “Due Date” is less than “Today” (again, meaning we’re currently past the “Due Date”) and the “Status” field is equal to “Completed”, the entire comparison is “False” which results in a “No” being displayed (True: False).
4. If the “Due Date” is not less than “Today” (again, meaning we still have time left) and the “Status” field is equal to “Completed”, the entire comparison is “False” which results in a “No” being displayed (False: False).

In the above four possibilities, the only result that will display a “Yes” is if we’re past the “Due Date” and the “Status” is anything but “Completed (each of the other three will either mean the task has been completed or we still have time left).

Testing this in Excel is as follows.

Enter a start date in the past and a due date in the future (notice the “Overdue?” column displays “No” indicating the task is currently not overdue):

Change the due date to be yesterday (notice the “Overdue?” column displays “Yes” indicating that we’re currently past the due date and that the task is overdue):

Modify the task again and set the status as “Completed” (notice the “Overdue?” column now displays “No” since the task is completed).

One extra thing to point out in the logic of this column is that it really only applies to tasks that are open. Once a task is completed we’re not concerned with it being overdue anymore, so after its completion it will always display a “No” (we’re using the “Completed Early?” column to inform us of tasks that were completed ahead of schedule, so adding in logic for that information on this column would be redundant).

To get this into SharePoint, we follow the same approach as before:

In SharePoint, go into the “Tasks” list and its settings Page.

Since we’re once again using a “Today” reference in our formula, we’ll need to create our temporary “Today” column that will allow us to use the reference without displaying any errors.

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

Next, create another new column called “Overdue?”, make it a “Calculated” type and paste in the formula from Excel. Before saving, replace “B7″ with “[Due Date]” and “B4″ with “Status” (making sure to include the brackets [] around “Due Date” so the system will recognize it as a column).

Formula in Excel:
=IF(AND(B7<TODAY(),B4 <> “Completed”),”Yes”,”No”)

Formula in SharePoint:
=IF(AND([Due Date]<Today,[Status] <> “Completed”),”Yes”,”No”)

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

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

Once saved, we can see that our new column displays a value of “No” indicating that the task is not completed and that we still have time to complete it before it comes due.

Our first check is to see if the “Due Date” is less than the current date. In this case, since the “Due Date” is tomorrow (greater than today, not less than), the result is “False”. The second check is looking to see if the value of the “Status” column is “less than or greater than” (equivalent to the statement “Not equal to”) the text “Completed”, which is “True” since the task is in fact not completed. Since both checks are not “True” (first is “False”, second is “True”), the entire check is “False” which results in a display of the text “No”.

Looking back at our “Truth Table“, we see that any combination of the two checks that results in anything other than the two of them being “True” will result in the entire check being “False”, which in this case, is our final result and displays the text “No”.

To test what other values could be displayed, we simply modify the details of the task to see what the results are.

Set the “Start Date” back a couple of days and the “Due Date” to today’s date (notice how the value remains “No”?).

Since the “Due Date” is not less than today’s date (it’s equal), the first check is “False”. The second check however, is “True” since the value of the “Status” field does not equal “Completed”, so since both checks do not result in “True”, the entire check is “False” resulting in “No” being displayed.

Modify the item again, but this time set the “Due Date” to yesterday (notice how the value now display’s a “Yes”?).

In this example, the first check is “True” since the “Due Date” is less than the current (”Today”) date. The second check is also “True” since the value of the “Status” field does not equal “Completed”. As a result of both checks being “True”, the entire condition is “True” which gives us a displayed result of “Yes” indicating that the task has not been completed and is currently overdue.

Modify the item once again and mark the task as completed (notice how the value now display’s “No”?).

For this one, without even looking at the first check we know immediately that this will display a value of “No”. Why? Because in the second check we’re looking at the value of the “Status” field, and testing to see if it “Does Not” equal the text “Completed”. Literally, if it equals anything but the text “Completed” it will be “True”. Since we’ve set the task as completed, it will now automatically become “False”. Because of this, no matter what possible value could be in the first check, the entire (combined) check will always be “False” for the simple reason that the second check is “False”. So, once the task is completed it is no longer overdue (logically, this should make sense).

Summary

Putting all of this together, let’s take a quick look at what we’ve accomplished throughout this series and how to make it an effective solution for making the “Tasks” list more useful.

Our five calculated columns we’ve created are:

1. “Date Completed” – Uses the “Today” reference to mark when a task was completed.
2. “How many days left to complete?” – Visual indicator of time left on a task.
3. “Days Overdue” – Advanced version of the “How many days left to complete?” column with better information.
4. “Completed Early?” – Visually indication of tasks completed ahead of schedule.
5. “Overdue?” – At-a-glance view of tasks that are overdue.

Through each of these articles, we’ve worked with multiple functions (some separate, some in concert with each other):

• IF – Conditional statement containing three parts (logical condition to check, value if true, and value if false) that allows us to display a particular result based on a check of a given condition.
• AND – Boolean operator that allows us to check multiple conditions at the same time where each individual check must equal true in order for the whole (combined) condition to result in true.
• TEXT – Conversion function that takes a value and converts it to text in a specified “number” format.
• INT – Math function that simply rounds a given number down to it’s nearest integer.
• ISERROR – “Error trapping” function that gives us the ability to decide what will be displayed if an error is discovered somewhere in our calculation.

And, we’ve also worked quite a bit with the “Today” reference and how to use it in a Calculated Column (despite Microsoft’s own documentation stating that it can’t be done) by using a temporary column to bypass the infamous SharePoint error messages.

In the “Out of the box” implementation of the Tasks list, the functionality is limited, as it only gives us information as to when the task is due, a percentage of its current completion and status.

By adding in our custom columns, we can now see if a task is overdue, how many days it’s overdue or have left, the date it was completed, and whether or not it was completed ahead of schedule. With these new columns, the possibility for custom views begin to surface allowing us more advanced ways of filtering tasks for the users they are assigned to, those that review the tasks, and those that are responsible for reporting and auditing task history after task completion.

(Simple Custom Views)

Example of custom view for assignees:

Example of custom view for mangers:

Example of custom view for auditors:

In future articles, we’ll be covering more functions and more advanced formulas that will give us even more power in how we view data (perhaps even a few more additions to the “Tasks” list).

Till next time…

– Dessie

Suggestions for future posts on calculated columns are always welcome, and if 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.

Dessie Lunsford
Points-of-Sharing
“I enjoy figuring out how to get my users to understand what they can do with SharePoint, and “how to do it” – this is probably the biggest hurdle my users have to face.”