First, I start with a custom list. I never use the templates in SharePoint because they are constantly trying to make things more complicated in an effort to make things more simple.
The Easy Stuff
- Rename the [Title] field to 'Task' or something more representative of the items you'll have on your Kanban board.
- Create a [Person or Group] field to contain the person responsible for the item. It's important that this not be a simple text field. I'll explain why when we build the views.
- Create any other meta data columns that you want for your items (notes, description, priority, estimated effort, etc.)
- Create a [Date and Time] field to contain the due date. Call it [Due Date] if you want to use the formulas here without modification.
- Create a [Date and Time] field for every phase. For example, if my phases were:
Deploy Launchpad, Igniter Primed, Mount Rocket, Connect Detonator, Remove Safety Cap, Detonate
Then I would create the following fields as [Date and Time] fields:
[Launchpad], [Igniter], [Mount], [Connect], [Safety], [Detonate].
Essentially, each field will contain the date and time that that phase was completed. If the date is blank, that stage hasn't been completed. If there is a value in the field, then that phase has been completed (and was completed at that date/time). - Go to List Settings >> Advanced Settings and disable attachments for the list (this was a dumb feature for what we're using the list for).
The Next Phase Calculation
Create a [Calcuated] column called "Next Phase". This column should evaluate the phase fields to determine which phase is the current phase being worked on. Continuing with my example, if I had already deployed the launchpad, primed the igniter, and mounted the rocket, the "Next Phase" would be to connect the detonator.This is done by evaluating the last phase to see if it is complete. If the last phase has a date/time value, it is completed and the next stage is "Done". If the last phase does not have a value, we need to figure out if the "next phase" is this phase or the previous one. Here's the formula (using my example field names, you should be using yours):
=IF(NOT(ISBLANK([Detonate])), "Done",
IF(NOT(ISBLANK([Safety])), "Detonate",
IF(NOT(ISBLANK([Connect])), "Safety",
IF(NOT(ISBLANK([Mount])), "Connect",
IF(NOT(ISBLANK([Igniter])), "Mount",
IF(NOT(ISBLANK([Launchpad])),"Igniter",
"Launchpad"))))))
Technically, the end of each line above can say anything you want. Since it would be nice to be able to sort the [Next Phase] column to show tasks in order, it would be nice if they were in some sort of sortable order. Unfortunately, alphabetical order won't work. We can easily fix this by prefixing each resulting string with a number to indicate the order, like this:
=IF(NOT(ISBLANK([Detonate])), "6 Done",
IF(NOT(ISBLANK([Safety])), "5 Detonate",
IF(NOT(ISBLANK([Connect])), "4 Safety",
IF(NOT(ISBLANK([Mount])), "3 Connect",
IF(NOT(ISBLANK([Igniter])), "2 Mount",
IF(NOT(ISBLANK([Launchpad])),"1 Igniter",
"0 Launchpad"))))))
The IF(NOT(ISBLANK( logically means, if the previous phase has a value but the current phase didn't, the current phase is the next phase.
The Status Column
This column is designed to figure out the status of each item as compared to the due date. Four possible states exist:
- If there's no [Due Date] (e.g. [Due Date] is blank), the the status is "No Due Date".
- If the item has not been completed (e.g. the last phase field is blank) and the item is not yet due (e.g. the [Due Date] is greater than today), its status would be "On Time for Completion".
- If the item has not been completed (e.g. the last phase field is blank) and the item is due (e.g. the [Due Date] is less than today), its status would be "Overdue".
- If the item has been completed (e.g. the last phase field is not blank) and the item was completed before the due date (e.g. the last phase field is less than the [Due Date]), its status would be "Completed On Time".
- If the item has been completed (e.g. the last phase field is not blank) and the item was completed after the due date (e.g. the last phase field is greater than the [Due Date]), its status would be "Completed Late".
Here's the formula:
=IF(ISBLANK([Due Date]),
"No Due Date",
IF(ISBLANK([Detonate]),
IF([Due Date]>=Now(),"On Time for Completion","Overdue"),
IF([Detonate]<=[Due Date],"Completed on Time","Completed Late")
)
)
The Views
I recommend 4 types of views:
- Datasheet View - This should be a datasheet view of all items. Usually sorted by [Due Date].
- All Items - This is a standard version view of the Datasheet View. You can alternatively add groupings based on Next Stage.
- My Items - This is either a standard or datasheet view (your preference), also sorted by [Due Date], however, filtered by the person the item is assigned to (remember above where I said we'd use this later?). SharePoint has a session variable called [Me], which contains the username of the current user. By putting a filter where the [Assignee] field is equal to [Me], we create a view that only shows the items assigned to the current logged in user. This means that anyone on the team can log in and look at this view and see only their items. This won't work if you made the assignee field a simple text string; it needs to be a [Person or Group] field.
- Phase specific views - these views aren't required but are often requested. You basically build a copy of the Datasheet View or the All Items view but filter it where [Next Phase] field equals a particular phase. You would repeat this for every phase. I find this tedious when those who want this type of breakdown could just look at the Datasheet or All Items views and just filter for a particular value in the [Next Phase] field. However, some people can't handle that level of sophistication, so statically defining views is the only way to please them.
