Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

I wrote a lengthy blog post comparing using Google Sheets as a project management “platform” relative to other dedicated project management software on the market. This post explores how to build a project management platform in Google Sheets. The functionality in this Google Sheets tool rivals that of some of the more popular project management platforms on the market (e.g. Microsoft Project). Here’s the Google Sheet with the workflow fully built out.

An unconventional tool for project management

Google Sheets is not the “platform” you might think of when it comes to typical project management workflows. In episode 43, I talked about how spreadsheets can be “extended” beyond its core use case (e.g. accounting, financial analysis) to tools and application. This project management “tool” is a perfect example of this concept.

Google Sheets might be the first thing your team reaches for because it’s free and allows real-time collaboration. With some basic formulas, you can build some pretty advanced functionalities and workflows similar to other SaaS tools and software. Here are the main features of the Google Sheet and how it works.

Step 1: Creating a task dependency column

Most projects have tasks with dependencies. In column A, we have all our tasks. In Column B, we have the name of the task that the current task depends on. Instead of copying and pasting a task from column A into column B every time we want to change what the dependent task is, we can use a data validation to get a dropdown of all the tasks in column A:

Now, any task that shows in column A shows up in the dropdown in in column B:

The issue is that if you change the spelling or name of a task in column A, it won’t carry through to the dropdown to the data validation cells in column B. So if “Instructor Shoot” changes to “Instructor Shooting,” cell B3 will still say “Instructor Shoot.” You have to manually click the dropdown again and select the new “Instructor Shooting” dependency. Not the end of the world assuming your dependencies don’t change often, but could be annoying if you have projects with tasks and dependencies that change often.

Step 2: Calculating task end dates

Column G, or the Task End Date, is simply the Task Start Date plus the Duration (Days) column. While this seems like a trivial formula, it’s actually not that “easy” in other project management platforms. The reason is that you are mixing a date format with a number format. In other platforms, you have to create a special formula column that uses some special function like DATEADD(). In Google Sheets, it just kind of works with adding a number (representing the number of days the task takes) to the Task Start Date:

Step 3: Calculating task dependency start and end dates

Moving over to columns H and I, we want to calculate the start and end dates of each task’s dependency task. The reason it’s important to calculate these dates is so that we know when the current task can start. We are assuming a basic Finish-to-Start dependency type where the current task cannot start until it’s dependency (e.g. predecessor) ends. In order to calculate the start and end dates of the task’s dependency, we do a VLOOKUP onto the same table using column B (the dependency task) as the lookup value and the lookup table is starts with column A (the main task) to column G (the task’s end date):

The dependency’s end date is similar to the formula above, except it return column G (7th index in the lookup table).

Step 4: Calculate task start date

We do column F last because the Task Start Date depends on when that task’s dependency ends. Therefore, Task Start Date simply equals the Dep End Date column. Some project managers may bake in a lag between the dependency task and the current task. This scenario assumes the current task can start right when the dependency task ends:

The reason we have all these dates is so that changing the Task Start Date for the first task will automatically cause all the subsequent task dates recalculate automatically. Cell F1 is a hard-coded date since that task does not have a dependency (hence why it’s the “kickoff” task).

Creating this “waterfall” date effect is difficult in some other project management platforms because it may result in a recursive formula situation. In project management platforms that have specific features around dependencies, this waterfall of dates is built into the software. On other platforms, you have to manually select the start and end dates of each task. This can be annoying if you want the same set of tasks and dependencies for each project you run.

Step 5: A hacky gantt chart visualization of tasks

Most project management platforms have a fully-featured gantt chart view of your tasks and dependencies. Typically these gantt charts allow you to drag-and-drop the ends of each bar (representing each task) to edit the start and end dates of each task. This may be useful if you want to control the start and end dates of each task, but in our case, we already know the start and end dates of each task due to the dependencies and durations of each task.

To build a simply visualization, you can add numbers across the first row (representing each day of the month). Then in the empty cells below, you have a formula that checks to see if the current day of the month falls within the task’s start and end dates. If it does, put an “x” in the cell:

Since each of these cells contains an “x” or is blank, you can apply some basic conditional formatting to get a waterfall view of all the tasks:

Fitting a square peg into a round hole

While Google Sheets or Excel may not be the best platform for this project management use case, it’s still a compelling solution when you need something that “just works.” For spreadsheet gurus out there, it’s kind of fun to try to write formulas and stretch Google Sheets beyond what it’s meant to do in order to solve a use case. Each of these scenarios poses a challenge for us to build a tool in an unconventional way.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• ShopTalk Show Ep #429: Cognitive Bias with David Dylan Thomas
• The Remote Show Ep # 31: Mesh Lakhani, Founding Partner at Mark 2 Capital and host of Talk Money