3

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
(Data)

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

Bar chart
(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
(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
(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?

2
2

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.

5
  • Thank you for your response. I am not able to add or modify the contents of the sheet, so I cannot add a duration. I am looking for a solution like the one presented for Excel in this question: stackoverflow.com/questions/42630916/…
    – jsejcksn
    Apr 29 '18 at 23:38
  • @jsejcksn Adding a bar chart is adding contents to the sheet. Are you also saying you cannot add a chart to the sheet?
    – Jerry
    May 2 '18 at 21:56
  • Hi @Jerry. By "contents" I meant the input data for the chart. I understand that this answer provides the kind of chart I'm looking for, but I'm wanting to know if there's a way to get it using only the existing input data. Thank you for pointing that out—I clarified that detail in my question.
    – jsejcksn
    May 3 '18 at 4:36
  • 2
    @jsejcksn I don't think there is a way to do that without adding the additional column. The answer you found on SO isn't entirely 'clean' in a way. For instance, gridlines get turned into white (no there's no way around that using the method in that answer there) and if there were more columns behind or in front, some of them might get completely hidden. There is however a different Q linked in that thread in a comment in the question: stackoverflow.com/a/17871094/1578604 which shows the proper way to do that, and that's using a duration (labelled as interval there).
    – Jerry
    May 3 '18 at 6:53
  • @I'-'I That's fair to a certain point; the one where there's so many bars that it becomes hard to see where one begins and ends. All I'm trying to say is that: to get proper results, there are intermediate steps that are required; just like, I don't know, how you need to process wheat to end up with a decent bread; you can try making a bread out of raw wheat, but it won't be a decent one.
    – Jerry
    May 3 '18 at 8:36
0

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:

enter image description here

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.