I was recently asked for assistance with this request:
Hi Steve,
I am trying to make a hypnogram with data that we collected over the course of a day. I want the y-axis to denote wake (W), other (O), and sleep (s) with just three tick marks. The y-axis should represent the time spent in these states.
A hypnogram plots sleep patterns over time and is used to asses a person’s wake/sleep behavior at night (or during the day). It is used for diagnostic and treatment purposes for individuals with sleep disorders.
Some sample data:
| W | 8:20:49AM | 8:21:04 |
| O | 8:21:05AM | 8:21:24 |
| W | 8:21:25AM | 8:21:54 |
| S | 8:21:55AM | 8:23:43 |
| W | 8:23:44AM | 8:23:57 |
| O | 8:23:58AM | 8:24:44 |
| W | 8:24:45AM | 8:28:11 |
| S | 8:28:12AM | 8:29:34 |
| W | 8:29:35AM | 8:30:31 |
| S | 8:30:32AM | 8:35:26 |
The second set of numbers is when the state ended.
Thank you,
Anna
You can learn more about our Excel step chart solutions here:
https://www.exceldashboardtemplates.com/how-to-easily-create-a-step-chart-in-excel/
https://www.exceldashboardtemplates.com/how-to-easily-create-a-step-chart-in-excel-2/
Even though you can use the tutorials above to help with this Excel challenge, there is a trick to working with a Time Series in Excel. So it might not be a easy as it may first appear.
Give it a shot and let me know if you were able to solve this challenge. I was able to solve Anna’s request and she was able to automate it with the formulas you see in the tutorials above. I might even get a mention in her publication if it is accepted 🙂 Way cool!
Steve=True
