Bar chart of time ranges in Google Sheets

Question

Overview

I have a spreadsheet with 3 columns [Name, Start time, End time] which is for scheduling purposes. I want to create a bar chart showing all of the people's availability as a series of ranges in one 24-hour period, but I'm not sure how to make it happen.

---

Examples

Here's the example of my data (there are more columns for the other days of the week:

(Data)

I can make a basic bar chart, but it shows both times as separate bars, starting at midnight:

(Bar chart)

If I change the chart type to stacked bar, then I'm getting closer, but now there are two 24-hour periods:

(Stacked bar chart)

And if I change the color of the start time to None, then I'm even closer, but it still won't overlap the data in a single 24-hour period:

(Stacked bar chart, transparent series)

---

Question

It seems I need to make it somehow overlap the data to show the ranges all in one 24-hour period.

How can I get the kind of chart I'm seeking?

EDIT: How can I get the kind of chart I'm seeking using only the existing input data?

Answer 1

The Stacked Bar chart, as the name implies, stacks up the given ranges.

In the Stacked Bar above,For Person 1,The blue bar starts at 00:00 and ends at 21:00. The red bar 23:00(C2) is added(stacked above the blue) to B2 to arrive at next day 20:00. Thus the 24hr period.

Solution:

Instead of End time in C column, Use duration available. If that's not possible, Use D1:

=ARRAYFORMULA({"Duration";C2:C4-B2:B4})

Now,Select Series 1 as B2:B4 and make it transparent. Series 2 as Duration:D1:D4. You'll get a single 24 hour period chart with available period marked in Series 2 color.

Answer 2

For others coming here trying to find how to do a stacked bar chart for durational stuff, where the result might wrap to the next day, the answer by TheMaster will likely get you somewhere close.

For listing Fitbit data, where each day has a single sleep time recorded, with start time (which can be in the previous day) and end time, I added a few extra columns.

• Col A: Start datetime
• Col B: End datetime
• Cold C, D: (Not relevant here)
• Col E: End Date calculated as =ROUNDDOWN(B2)
• Col F: Time before sleep, as =IF(A2<E2,0,A2-E2)
• Col G: Morning sleep, as =IF(A2<E2,B2-E2,D2/(24*60))
• Col H: Awake time, as =1-(F2+G2+I2)
• Col I: Evening sleep, as =IF(A3<E3,E3-A3,0)

I took the data from downloading all my FitBit account history, wrote a script to convert it from JSON to CSV, add in lines of zero-length sleep for any missing end dates, and sort it.

Then I pasted that data into columns A-D.

Adding columns F-I as Series to a Stacked Bar Chart, with Series 1 (column A) and Series 3 (column C) set to have 0% opacity, and the other two both the same color, I got an excellent graph of my sleeping patterns.

If you might have multiple events per day, you'll need two columns per possible event, plus two extra for midnight-overlaps, each of which needs to be added as a Series to the graph. In my case, one event per day, so four columns for the four Series in the graph.

---

I went a step further, as I wanted weekends to be displayed in a different color; so I made 8 more columns, which held two copies of those four columns. The first copy, columns J-M, were the "weekday" entries, populated with:

=IF(WEEKDAY($E2)<6,F2,0)

The second copy, columns N-Q, were the "weekend" entries, populated with:

=IF(WEEKDAY($E2)<6,0,F2)

That gave me this spreadsheet:

Then, I replaced my original 4 Series in the graph with these 8, set Series 1, 3, 5, and 7 to 0% opacity, 2 and 4 to green and 6 and 8 to green.

This gave me this graph: