Create Bar Chart in Excel with Start Time and Duration
I want to create what is essentially a Gantt chart in Excel 2013 with time of day as the x-axis, date as the y-axis, and floating bars representing time durations. Example data:
Date Start End Duration
21-Apr 6:45 8:20 1:35
21-Apr 10:00 11:30 1:30
21-Apr 14:00 16:00 2:00
22-Apr 6:50 8:40 1:50
22-Apr 11:00 11:45 0:45
22-Apr 14:00 15:00 1:00
23-Apr 6:50 8:40 1:50
23-Apr 10:50 12:15 1:25
23-Apr 14:00 14:45 0:45
So my chart would have three rows (one per date), each of which would have three separate floating bars representing the three time durations that day.
I know the solution involves inserting a stacked bar chart and making the Start series invisible, but I can't get it to work. Help?
Answer
Charting differs between different version of Excel and if not using something like VBA is probably best asked about on Super User if not already a question asked there (answers/guidance there may be more detailed than on SO) but since you clearly have a good understanding already maybe an example for Excel 2013 will get you started.
First, it often seems easier to arrange data in the way Excel expects, so that default settings are mostly applicable, rather than to make all the adjustment manually. So to start with I have changed your layout, preserving Start but converting Ends to durations throughout. For the sake of the image I have also chosen rows for the dates.
Select A1:G4 and as I think you are on the right lines with a Stacked Bar chart click that. Then click in the Plot area, Select Data… and Switch Row/Column. Select each series (colour) in turn and format to suit (which probably means No fill for the first series).
With luck the X-axis will be formatted suitably because being times it may otherwise be fiddly to adjust.
