Gantt charts in Microsoft Excel
Posted by Tariq • Tuesday, December 16. 2008 • Category: Computers, Rants
As mentioned in other articles I sometimes like to output data in CSV format and redirect to a file and then open that file with my favourite spread sheet application to create pretty charts instead of using awk to create command line graphs or gnuplot. However I recently found out that Microsoft Excel doesn't do Gantt charts by default, so what follows is an explanation of what I was trying to do and how I simulated a Gantt chart in Excel.
We are hosting a number of databases which are in use for only periods of time during the day. So for instance our US database is only used for working hours across the US. I wanted to represent the times in which a database was in active use using a Gantt chart. To do this I created a table like the one below.
This looks way too complicated! We should have only Usage start (GMT offset) and day duration for a Gantt chart? Well, yes, but no. I want to show the number of overlapping usage in a 24 hour period and because there is no Gantt support I have to hack it a bit. So let me explain each of the columns.
Normalized by - This is the lowest value in the Usage start (GMT offset) column less than zero. This value is multiplied by -1 to make it positive.
DB - The database in use.
Usage start (GMT offset) - The earliest time in which this DB is in use, given by GMT offset.
Duration - The amount of time the database is used for.
Day start - Usage start + Normalized by.
Day end - Day start + Duration.
Day wrap start - Always zero.
Day wrap end - If Day end mod 24 is less then Day start then this value is (Day end mod 24); else 0. All my durations are less than 24 hours.
Day wrap dur - Duration minus Day wrap end.
Day wrap diff - Day start minus Day wrap end.
This all seems superbly weird until I mention that we will be using a stacked bar chart to simulate a Gantt chart. In our stacked bar chart we have three values which can appear:
To make our Gantt chart the second data series will be made invisible. We also need to set the colour of the third data series to the same as the first.
Here is one I made earlier:
Edit: This is a draft, I have no idea why it exists. Publishing this is a karma -3 but hey it may be useful to someone.
We are hosting a number of databases which are in use for only periods of time during the day. So for instance our US database is only used for working hours across the US. I wanted to represent the times in which a database was in active use using a Gantt chart. To do this I created a table like the one below.
| Normalized by | 7 | |||||||
|---|---|---|---|---|---|---|---|---|
| DB | Usage start (GMT offset) | Duration | Day start* | Day end* | Day wrap start | Day wrap end | Day wrap dur | Day wrap diff |
| US | -7 | 10 | 0 | 10 | 0 | 0 | 9 | 0 |
| ... | ... | .. | ... | ... | ... | ... | ... | ... |
This looks way too complicated! We should have only Usage start (GMT offset) and day duration for a Gantt chart? Well, yes, but no. I want to show the number of overlapping usage in a 24 hour period and because there is no Gantt support I have to hack it a bit. So let me explain each of the columns.
Normalized by - This is the lowest value in the Usage start (GMT offset) column less than zero. This value is multiplied by -1 to make it positive.
DB - The database in use.
Usage start (GMT offset) - The earliest time in which this DB is in use, given by GMT offset.
Duration - The amount of time the database is used for.
Day start - Usage start + Normalized by.
Day end - Day start + Duration.
Day wrap start - Always zero.
Day wrap end - If Day end mod 24 is less then Day start then this value is (Day end mod 24); else 0. All my durations are less than 24 hours.
Day wrap dur - Duration minus Day wrap end.
Day wrap diff - Day start minus Day wrap end.
This all seems superbly weird until I mention that we will be using a stacked bar chart to simulate a Gantt chart. In our stacked bar chart we have three values which can appear:
- Day wrap end.
- Day wrap diff.
- Day wrap duration
To make our Gantt chart the second data series will be made invisible. We also need to set the colour of the third data series to the same as the first.
Here is one I made earlier:
Edit: This is a draft, I have no idea why it exists. Publishing this is a karma -3 but hey it may be useful to someone.
0 Comments
Add Comment