In this article, we will use Gannt chart to display the event timeline of a parent job (i.e., "Creating Application"), which was processed by its child jobs asynchronously.
Gantt Chart in Excel
As shown in [2, 3], there is no direct support of Gantt Chart in Excel. However, you can use Stacked Bar to achieve the same effect. In both referenced articles, they use date and duration (in days) as example. Here we will show you how to use time and duration (in hh:mm:ss) to draw the Gantt chart.
|Start Time||End Time||Duration (in secs)||Duration (in hh:mm:ss)|
If you select the cell "8:49:57" and check its format by selecting "Format Cell", you would find that Excel automatically assign the "h:mm:ss" format to the cell.
The main issue of drawing Gantt Chart in hh:mm:ss format is that
How would you represent your duration?To start with, I have tried the following formats:
- 134 (secs)
By trial-and-error, it turns out that Excel only like the third format. If you chose the first format, Excel would treat it as 02 hours and 14 minutes.
Drawing StepsWithout much ado, I would urge you to watch this video for the how-to. Here we only highlight the steps that are unique to this Gantt Chart.
In the Select Data Source dialog, click Add button under Legend Entries(Series), and then an Edit Series dialog will appear:
- Click button under Series name to select the Start Time cell, B1 in this case.
- Click button under Series values to highlight your time cells (from cell B2 to cell B7 in this example), see below:
You're almost finished. You just need to remove the empty white space at the start of your Gantt chart. Click on the first Start Time (i.e., 8:49:51) in your data table. Right click over it, select Format Cells, then General. Write down the number you see. In my case it is 0.367951389. Hit Cancel because you do not want to actually make any change here.
In the Gantt chart, select the time labels below the bars, right click and choose Format Axis. Change Minimum to Fixed and enter the number you recorded (i.e., 0.367951389). Do the same for Maximum by entering 0.370868056. Change Major unit to Fixed and enter the number 0.0005. You can play with this delta to see what works best for you.
- Make sure both your "Start Time" and "Duration" cells have the "h:mm:ss" format:
- Format Cell > Custom > h:mm:ss
- Click on axis label > Right click and select Format Axis > Axis Options > Categories in reverse order
- For example, otd-add-app-config is the first task executed in the above diagram and other tasks (except the parent task "Creating Application") depending on it. So, it might be a good idea to move it to be the first row and you can remove "Creating Application" parent task, etc.