2

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

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.

  • 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

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.