Graph showing sleep data

I didn't look closely enough at your chart. Aside from the 24 hr problem, you cannot make a chart where the Y axis has a larger number on the bottom than on the top. You will hve to create your Y axis value labels manually. Also, to get below "0:00", the chart cannot be plotting Date & Time values because there is no such thing as negative time.

I consider "sleep time" to be the ending of the day. From that viewpoint, it appears for each date you are entering the previous day's sleep time and the current day's wake time. The vertical distance between the two on the chart therefore indicates the amount of time you slept. I moved my columns around accordingly.

As explained above, I had to use durations like Jerry did. Columns B, C, F, and G are formatted as durations.

You don't necessarily have to create three new columns. You really only need the one that adjusts the sleep time. But I find it much easier and less error prone and it often takes fewer steps to create charts when the data is all together. You can do it either way.

column E = A

column F = B- DURATION(,,IF(B>C,24,0))

column G = C

Make the scatter chart then do the following:

• You need to set the Y-axis min and max, you cannot let them be "auto". For the chart in the sceenshot, the max is 16h and the min is -4h and there are 5 steps.
• Resize the chart to whatever size you want it to be.
• The Y axis of the chart is actually a table. A plain table, one column. Type in the same Y axis labels that the chart made except for the lowest one which, in this case was a -4:00 and needed to be 20:00.
• Turn off the chart's Y axis labels and position and resize the table to be the Y-axis labels.
• Turn off the table borders and set the fill color to none.

Here's my suggestion for you, Sebastian.

The Max for your chart value can be more than a day. I suggest that you designate bed times beyond midnight to be part of the next day, timewise. I'm running this chart from 6am to 6am, across Midnight.

For bed times past midnight, enter then as 1d and xhours. A bed time of 2 am would become, in Duration, 1d 2h.

Jerry

I would say you need to convert the time to a numeric value using the function timevalue(). This function is described in the free user guide whoch you can download from Apple here:

http://support.apple.com/manuals/#productivitysoftware

and in the function broswer you can open in Number by selecting the menu item "View > Show Function Browser"

The way Numbers handles Date & Time values can be helpful sometimes and painful other times. The problem is that the wake and bed "times" you are entering get tagged with the current day's date. You need to convert them to all be on the same date. What I would do is this:

1. Lets say Table 1 is your data with the date in column A, wake time in B and bed time in C
2. Add three more columns to your table. Lets say they are columns E,F,G
3. Column E = A
4. Column F = TODAY() + TIMEVALUE(B)
5. Column G = TODAY() + TIMEVALUE(C)
6. Select all three columns and plot as a scatter chart. At first it will only plot the wake times. You will have to click the gear and choose "share X values".
7. In the chart inspector, choose "show hidden data"
8. Hide those three columns when you are done

Hi enigma,

Dates and times are sometimes tricky things to work with. Follow the advice from Wayne and Badunit.

Just for fun, have a play with the Numbers Template "Employee Schedule" to give you some ideas on how Numbers handles Dates and Times.

In Numbers, go to Menu > File > New from Template > Employee Schedule

Open Inspector > Cells and click here and there in the table to see how the formats change from Pop-up Menu (where you choose a time) to Duration (a time difference).

This is not an answer to your question. Simply for you to have a play.

Regards,

Ian.

Unless something has changed in that template, it also had problems regarding date & time values. The issue in that template was that if you tried to modify or add to the date & time popups, the new additions would have the current date while the old entries would keep the old date (ie., the date when the designer entered those dates in the popup) and then the time calculations will be way wrong. So, basically the same problem. There were a number of posts on this a long time ago which involved modifying the formulas to use TIMEVALUE.

Sebastian,

Using Duration for your wake and sleep times should work for this application if you don't mind the 24hr format:

Jerry

Hi Sebastian,

Here is a thought that might get around the "midnight starts a new day" problem. Numbers (and every app and every computer clock) begins a new day at 12:00 am. That is nonsense (to humans). That displayed time could also be 0:00 hours or 24:00 hours. If a mechanical clock shows 12, is that one millisecond before midnight or one millisecond after midnight?

Instead of considering that each new "day" begins at midnight, it could be more useful to consider that each "night" begins at some other time (such as sunset).

Choose any time as your starting point for a night. It could be 6 pm.

Suppose that you go to bed when the clock shows 11:00 pm. That is 5 hours after the arbitrary start of "night". If you go to bed when the clock shows 2:30 am, that is 8.5 hours after the arbitrary start of "night". Those values (5 hours and 8.5 hours) are numbers (5 and 8.5) and are not Date-and-Time or Duration format. They are *numbers* and can form one axis in your graph.

Ian.