By
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
