How can I prevent a Time formatted cell from also having a date in it?

You could consider laying out your data table something like this:

Columns A, B, and C are for input. They are formatted as Date & Time. Time is hidden in Column A, Date in Columns B and C.

In D2, filled down the column:

=DAYNAME(WEEKDAY(A2))

In E2, filled down and right to column F:

=DATEVALUE($A2)+TIMEVALUE(B2)

In G2, filled down:

=DATEVALUE(A2)

In H2, filled down:

=MOD(F2−E2,"1d")

When you set it up this way you can just enter the "dates" and "times" in the input columns and not worry about the hidden values that might disturb your calculations and charts. The MOD automatically accounts for a possible crossing midnight problem, but of course doesn't anticipate two all-nighters in a row!)

You can of course hide E, F or other columns after everything is up and working.

The chart in the example is 2D Scatter, something like this:

The Pivot Table (via Organize > Create Pivot Table > On Current Sheet) is set up like this in the example:

A Pivot Table takes maybe 2 minutes to set up (no formulas!) and can be quickly configured in any which way. Want Maximum or Minimum or fancier? Just add them.

And of course you can also make a chart based on a Pivot Table.

Intro to pivot tables in Numbers on Mac – Apple Support (UK)

SG

Date & Time format is always date & time. Pre-format the "times" columns as text. If you ever need to put the time of day with the date:

If A2 is the date (at the default 12:00AM) and

B2 is the time of day (as text)

then A2+TIMEVALUE(B2) will be the date & time

I don't know what your chart is exactly but you are right that a chart of text data doesn't get you anywhere. I'm kind of surprised Numbers doesn't convert it to date&time in the chart. It will convert text formatted numbers to actual numbers when they are charted but I guess not time of day.

One option is to create additional columns where you convert the text time to date&time with today's date

=TODAY()+TIMEVALUE(B2)

Chart those columns instead. You can hide them after you get it all set up. Make sure "hidden data" is checked for the chart or it will go blank again. Alternatively, you can make those columns a separate table, use that table for the chart, and put that table on another sheet.

If you are keeping it as date&time,

• Change the formulas to be like =DATE(1,1,1)+TIMEVALUE(B2) , using the correct cell references of course. The columns need to have a steady date so that you can set the min and max on the Y axis. TODAY() changes every day but 1/1/0001 is always 1/1/0001.
• Set Y min to 1/1/0001 0:00
• Some choices for Y max and steps:
  • 1/1/0001 22:00 with 11 steps
  • 1/1/0001 21:00 with 7 steps
  • 1/1/0001 20:00 with 10 steps
  • 1/2/0001 0:00 with 12 steps (top number on Y will be 0:00 but all 24hrs will be on the chart)

Thematteartist wrote:

I haven't have any problems at all with calculating the time slept or graphing that. It was graphing the beginning sleep time and the waking time that was causing me problems.

To give you more ideas I've added some columns and a chart:

The trick, as you no doubt have discovered by now, is to "normalize" the times of day for the y-axis so they're all on the same date. Today is as good as any, and updates automatically, so I do that here with this in I2, filled right and down:

=TODAY()+TIMEVALUE(B2)

The vertical lines in the chart are created with Error Bars referring to the 'Seconds slept...' column (K). In K2, filled down is:

=DUR2SECONDS(H2)

I couldn't find a way to make the error bars work well when crossing midnight. To turn them off click them in the chart and choose 'None' in the dropbox in the inspector pane at the right). Without them the chart should still be useful to show the patterns.

Charts in Numbers are endlessly versatile. But they're also fiddly and time-consuming to create. It can take a lot of experimentation to get them to do the right thing. (Changing the steps in the Axis tabs is particularly important). And who knew you can add seconds to a time to create an error bar?

To save time recreating any of this, here is the Numbers document for this example (Dropbox download).

SG

If the result of your formula is increasing in date, something is wrong with your formula. TODAY() gives today's date at 0:00. TIMEVALUE can only be 0 to <1 (corresponding to the decimal fraction of a day). Together they cannot create another date.

Actually, the chart brings up a different problem. Formatting the Y axis so it looks good may not be possible with the times as date&time. So.... change of plans. First though, if the Y axis goes from midnight to midnight do you want it to be 0:00 to 0:00 (counts up then the top number is 0:00, which is the next day) or 0:00 to 24:00? The first can still use date&time. The second I am thinking of converting the times to durations.

Hi Thematteartist,

It seems like the "Crossing Midnight" problem. To illustrate:

IF(C4<B4,C4−B4+"1day",C4−B4)

Regards,

Ian.

Edit: I did not see your screen shot. My reply may not apply. Ian.