In my opinion Numbers for Mac is not as powerful or versatile as Microsoft Excel. However, this versatility of Excel is hardly required for my personal home use. This Gnatt chart here illustrates my point. I could use it to track any of the (fictitious) mini projects that I (do not) take up from time to time at home.
I use a nifty trick I picked up from chandoo.org a few years back, and “ported” it so to say to mac for numbers. Of course in comparison to Excel’s conditional formatting capabilities, Numbers’ conditional formatting is woefully inadequate. Nevertheless the end result is not that displeasing to the eye.
- First create a Project Overview table, with an estimated start date and estimated end date.
- Now create the second table called the Gnatt chart table, start with four columns – Task, Start Date, Duration, and End date
- The columns End date should contain the following formula =WORKDAY(B3,C3), i.e. the working day corresponding to the “duration in days” i.e. C3 after the start date, i.e. B3.
- Add as many columns after this as you please, put a number to each of these columns. I tend to work with calendar weeks, so I have used calendar weeks. In this example 11 and above.
- The calendar week columns contain the following formula =IF(ISBLANK($B3),,IF(AND(WEEKNUM($B3)≤E$2,WEEKNUM(DATEVALUE($D3))≥E$2),1,0))
This formula does the following
- If the “Start date” column is not filled then do nothing
- If the calendar week value lies between the start and end date, then put a “1” to the column
- Else put a “0” to the column (it could even be blank actually!)
Now you should see a table which looks like this.
Firstly one must make the numbers “invisible”. Quiz time! How does one make a cell content invisible?
- Google it
- Set foreground and back ground colour to white
- Everything is always visible – it is all about perspective.
Once you have done that, you can move on to the conditional formatting part of this little trick and add this rule to the “Gnatt chart area” to see the simple chart you saw in the first figure.
And you are welcome!