using dates with stacked bar chart

Question

I'm trying to create a stacked bar chart to show the start and end times graphically. It works if I represent the times as numbers, but then on the X axis it is unclear what the numbers mean. For instance, if the intervals are in seconds it is difficult to mentally add that to the current time to figure out when the event starts and ends:

To be clear, the above chart does exactly what I want it to, except show the x-axis as date/time values.

But when I switch to using dates, the chart shows totally bogus values: each start time is shown extending from 1900-01-01 00:00:00 to approximately the current year (some time between 2000-01-01 and 2050-01-01) and the end show up as some time close to 2150.

If on the other hand, I use the start as a date and an additional duration (expecting that perhaps sheets is internally converting the date into something like an Epoch seconds count) it no longer shows a date, and still shows non-sensical results (to me) with everything apparently starting at the same time, past forty some thousand: I tried editing the chart, looking for some way to set the formatting and range of the X axis. I found that when the axis was a number I could set a minimum and maximum, but when the X axis was a date this option disappeared (although it seemed to be retained at least partially because the part of the date was omitted). But still, why in chart 2 does it show all charts starting and ending at the same points, when they shouldn't?

Answer 1

Create three columns: label, starting time, and duration expressed in days. You will need to format the duration in a strange way later on, so you may want to create a fourth column showing the duration in a more recognizable format such as hours.

Before creating the chart, you will need to format both the starting time and the duration in plain numbers. This is so you can set the Min value on the chart. Now insert a “Stacked Bar Chart” as shown in the image below:

Next, find the minimum value in the start column and under the Customize tab set the min value according to this. If you want to start at the beginning of the day, truncate this to an integer.

In the above image, the red represents the time frames we are interested in. The blue is superfluous, showing the beginning of time until our starting time. Get rid of it by going to Series, selecting it to apply it to your start column, and then changing the color to None:

The thing that is left to do is to change the axis so that it shows dates instead of numbers. Since you are actually showing the duration, that is the column whose format must be changed. As such, the duration will look a bit weird as you can see in the image below (this is why we created the separate column with the format we wanted to see duration in):