Graph Displaying an Individual's Sleep Data (Sleep/Wake Times) for 1 Year

[coelacanth]

The SleepBot mobile app allows users to log their sleep times. I've been doing so for over a year now. The output includes a table of start and end times, but it's organized like this, with the Date referring to the Sleep Time, not the Wake Time.

<table border="0" cellpadding="0" cellspacing="0" width="267"><colgroup><col width="75"><col span="3" width="64"></colgroup><tbody><tr height="20"> <td style="height:15.0pt;width:56pt" height="20" width="75" align="center">Date</td> <td style="width:48pt" width="64" align="center"> Sleep Time</td> <td style="width:48pt" width="64" align="center"> Wake Time</td> <td style="width:48pt" width="64" align="center"> Hours</td> </tr></tbody></table><table border="0" cellpadding="0" cellspacing="0" width="267"><tbody><tr style="height:15.0pt" height="20"><td class="xl63" style="height:15.0pt; width:56pt" height="20" width="75" align="right">2/20/2012</td> <td class="xl64" style="width:48pt" width="64" align="right">19:50</td> <td class="xl64" style="width:48pt" width="64" align="right">5:00</td> <td style="width:48pt" width="64" align="right">9.16</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">2/21/2012</td> <td class="xl64" align="right">22:20</td> <td class="xl64" align="right">5:00</td> <td align="right">6.66</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20" align="right">2/22/2012</td> <td class="xl64" align="right">20:52</td> <td class="xl64" align="right">6:15</td> <td align="right">9.38</td> </tr> </tbody></table>
The app also allows you to view an assortment of graphs, my favorite being this one:

(apparently I don't take naps in the summer).

Unfortunately, you can't edit the graphs they produce within the app, they're exported as images. How can I use the data to make my own, editable version of this graph (Excel 2010)?

 

[coelacanth]

Or maybe this isn't possible in Excel?

 

[Jon Peltier]

The data you showed is total time asleep and awake, not sufficient to show what you want.

Make a table with one row per date. Then add pairs of columns showing "time when I fell asleep" and "time when I woke up", one pair for the maximum number of segments of sleep you have each day. These columns need times either as formatted times or as fractional numbers, starting with 0 at midnight at the start of a day, ending with 1 at midnight for the end of that day. If you were asleep before midnight, put midnight (i.e., 1) for end of the last sleep period of the first day and midnight (i.e., 0) for the start of the first sleep period of the second day.

Make another table with the same number of rows and columns. This will have formulas pointing to the first table, but it will show durations. The first numerical column will equal the first time falling asleep, but the next will be duration of first sleeping period, then duration of first waking period, duration of second sleeping period, etc.

Finally, make a stacked column chart of this second table of data.