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
- Col F: Time before sleep, as
- Col G: Morning sleep, as
- Col H: Awake time, as
- Col I: Evening sleep, as
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:
The second copy, columns N-Q, were the "weekend" entries, populated with:
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: