News

Manage your time with a project tracker

Create a Gantt chart using a table, a few formulas and a little creativity to boost organisation

Main

You don’t need a project management suite when you’ve got Numbers – you can custom build your own tool for the job straight from the comfort of your iPad.And it’s a great way to practice your formula skills. What we’re going to do here is set up a table containing some projects and then make sense of the schedule by using a chart and a series of logic tests.
There will be an optional step involving your Mac, but for the most part we just need to know our objective: to plan when each project should start, based on the duration and end date of the project that must be completed first. Here are nine tutorial steps to get a greater understanding of just how useful Numbers can be.

Step 1

1 Set the variables

Make a new table as shown; set Start and Duration values for six projects, setting the columns to Date and Number format, then add an End column set to Date.

Step 2

2 Account for weekends

Get the End value by entering = WORKDAY (StartProject, DurationProject, [Blank]), where [Blank] is the empty exclude-dates field (you can use this to handle holidays).

Step 3

3 Start the chart

Make a Stacked Bar chart using just the Start and End dates. Your chart should show all the project durations in the second half of each bar, from their start to end points.

Step 4

4 Make it Gantt

You can’t do this bit on iPad, so save the document and open it up on Mac (via iCloud). Change the colour of the first half of each bar (the Start column) to No Fill.

Step 5

5 Visualise projects

Open it up on iPad and your Gantt chart clearly emerges. Now you can easily change the Start dates of each project depending on which other projects need to be completed first.

Step 6

6 Chain the projects

Create a Precedent column, filling in the number of the project that must be completed beforehand (eg Project 1 before Project 3). Add a Project number column at the front.

Step 7

7 Check if blank

Add a new formula in the next new column: = NOT ISBLANK PrecedentProject. NOT reverses ISBLANK’s true/false check on PrecedentProject, which is the adjacent cell.

Step 8

8 Get Precedent End

Now we find PrecedentProject’s End date: = LOOKUP PrecedentProject, ProjectColumn, EndColumn. This checks the first column to find the corresponding cell in the second column.

Step 9

9 Is Start valid?

Finish with a logic test to check if PrecedentEnd is before this project’s Start: = AND PrecedentEnd < Start. Use the true/false result to help you fine-tune your Start dates.

×