How to set X-axis to "Date" ?

Question

Level 1

57 points

How to set X-axis to "Date" ?

I'm tracking my weight (y-axis) over time (x-axis). It was working prior to upgrading to Mavericks. Now, The x-axis is no longer displayed as dates - they are very long numbers (eg. 390000000 instead of April for instance).

If I click on the scatter plot chart, then click on "Axis" in the right-hand toolbox, I can see an area called "Value Labels." This is while X-Axis is selected. It is set to "Same as Source Data." Clicking on it, I can see I can change its value label to Number, Currency, Fraction, Percentage, Scientific, or None. Duration, as well as Date & Time are greyed out and I cannot select them no matter how many times I click on it and no matter how hard I click the track pad.

How can I set the x-axis to Date & Time like before?

Posted on Oct 24, 2013 2:23 PM

Reply

Answer 1

Alias7 Author

Level 1

57 points

Oct 24, 2013 2:31 PM in response to Alias7

My dates are in column B, and weights are in column C and both columns have a white background when the 2D scatter plot chart is not selected. When I click on the scatter plot, column B is darker than column C, so maybe that is some kind of hint.

I tried selecting all of the date cells, then clicking on "Cell" in the right side toolbar, then changing the "Data Format" to "Date & Time" and then recreating the 2D scatter plot. Unfortunately, the x-axis still comes out as big numbers instead of dates, and the "Date & Time" option under the x-axis's "Value Labels is still greyed out.

Hmmmmmmm...

Reply

Answer 2

SGIII

Level 8

37,562 points

Oct 24, 2013 2:33 PM in response to Alias7

Hi Alias7,

You using the new Numbers 3.0, right? If so, this is a definite problem. We're all hoping it's just a bug and not a feature removal. I *think* Numbers 3.0 is accepting the dates ok but just not displaying them correctly on the axis.

It would help if you could give your feedback to Apple on this so they know it's a problem for a lot of us:

When I give feedback I've been putting "Numbers 3.0" in the subject line as I don't think they have a pick for that yet.

SG

Reply

Answer 3

Alias7 Author

Level 1

57 points

Oct 24, 2013 2:37 PM in response to SGIII

Yes it is Numbers 3.0. Luckily, the previous version of Numbers wasn't deleted/replaced, so I will just use it instead.

Reply

Answer 4

Oct 28, 2013 7:26 AM in response to Alias7

My almost 4-year old spreadsheet of tracking gas prices over time was broken in the same way by this "upgrade". After years of sticking with numbers because it was supposed to be the more user-friendly, prettier version of excel, I am going back to excel full-time. I understand that there will be bugs in any new version, but this is one of the most fundamental functions of a spreadsheet program. Very disapointed.

Reply

Answer 5

SGIII

Level 8

37,562 points

Oct 28, 2013 8:14 AM in response to Jokes_by_Morn

As long as you don't need/want to take your documents with you on an iPad, Excel is tough to beat. Numbers 3 isn't there yet. But the price is right and maybe it will get better.🙂

SG

Reply

Answer 6

Oct 31, 2013 4:34 PM in response to SGIII

I too have this problem and have submitted feedback to Apple. I hope they sort it out quickly

Reply

Answer 7

Yellowbox

Level 7

21,939 points

Oct 31, 2013 9:19 PM in response to Alias7

Hi Alias7,

A workaround until Apple fixes this. Use Duration (day of year)

Formula in C2 and Fill Down:

=B2−B$2+1

The blue triangles are because "Duration units were removed" Funny that, I will have to think what that means.

Anyway, the graph works. Need to alter the number of steps on the X axis to get sensible numbers. Auto seems to work.

Regards,

Ian.

Reply

Answer 8

Yellowbox

Level 7

21,939 points

Nov 1, 2013 12:36 AM in response to Yellowbox

Or Week number

Formula in C2 and Fill Down

=WEEKNUM(B2,first-day)

first-day is a parameter (Sunday or Monday as first day of the week). May not be important to set it for your graph.

Regards,

Ian.

Reply

Answer 9

Jan 28, 2014 8:26 AM in response to Alias7

As I just encountered this one, I think I found something that seems to do what you are looking for. You need to set the "Label References" field on the "Axis" tab under "Category X" to get these. You should be able to use a table/cell reference to do what you need. This is using Numbers 3.1 on a Mac.

Reply

Answer 10

Jan 28, 2014 8:34 AM in response to Alias7

The charts with Date as the X-axis that worked in Numbers V2.3 now work with Numbers 3.1. The latest update appears to have corrected the problem.

I kept a copy of Numbers V2.3 so that I could have my charts displayed correctly. Now I can use them in V3.1

Reply

Answer 11

Alias7 Author

Level 1

57 points

Jan 28, 2014 2:37 PM in response to Alias7

Its now working after the Numbers update as Keith pointed out.

Two simple issues!!!!: How do I get it to display just "Jan, Feb, Mar, Apr, etc..." on the x-axis? And how can I have it so that it doesn't skip any months!

It seems I only have the unabreviated option available, and the spacing is forced to be constant, resulting in skipped months!

In x-axis, I have set the scale Max to 5/1/2014 and Min to 11/1/2012. Whatever I set the Major to, it results in skipped at least one skipped month. I have the "Date" format set to "January", since "Jan" is not available. If there was a way for the Major lines to be labelled with the abreviated month name at the 1st of each month, that would be great, even if the lines were not evenly spaced. Does anyone know how to do this?

Reply

Answer 12

Jan 30, 2014 8:28 AM in response to Alias7

A7,

There's some trickery here. If you like the result, the time required may be worth it to you.

Column B contains: =LEFT(A, 3)

It's purpose is to create the abbreviations. Copy the column, Insert a Text Box and Paste and Match Style into the Text Box. I added New Paragraph marks between the abbreviations and Tabs to move the text to the right and set the alignment to Right Aligned. Rotate the box 90°.

The Chart is a Scatter Chart. In an improvement over Numbers V2, you can select to have more than 10 Steps in a Scatter Chart. This Chart has 11.

Min for X is set to the starting date and Max is set to the last date. If you want space to the right of Dec, add another step.

Set the Fill of the Text Box to White and position it over the full date values on the X-Axis label field.

Jerry

Reply

Answer 13

Alias7 Author

Level 1

57 points

Jan 30, 2014 12:35 PM in response to Jerrold Green1

I just typed out all the abbrevs by hand since I have multiple entries per month. If I could use line spacing of 3.75, that would work best, but it has to be either 3.7 or 3.8 which is too little or too much, respectively. I couldn't find a spacing/font size that works perfectly, but this is good enough.

Luckily my line trend is generally in the downwards direction unlike your example 🙂

Reply

Answer 14

Jan 30, 2014 12:38 PM in response to Alias7

You can adjust line spacing by adjusting the font size. Even easier is stretching or shrinking the Chart size a bit.

Jerry

Reply