How to set fixed days of month for multiple months?

Hi 4,

Dates in Column A. Formula in B2 (and Fill Down) is:

=DAY(A2)

That will give a number corresponding to the day of the month.

B is a Header Column, as required for a Line (category) graph. Select Columns B and C then insert your graph.

Regards,

Ian.

Here are two examples. Is this what you are looking for?

Example 1

Example 2a

Example 2b

Both charts use the same data set (for Y values), contained in columns C (Jan) through N (Dec) in the table below.

Example 1 is a Line chart. This is a Category chart—th Y axis is a value axis, and uses the values listed in columns C through N, the X axis is a category axis, and uses the Category Labels in column A (a Header column) as category labels on the X axis of the chart. (Column B is included on the table for use in the second set of examples, and is NOT used in this chart.)

Examples 2a and 2b are Scatter Charts. Both axes are value axes. The Y values are contained in columns C through N. The X values are listed in Column B (a non-header column), and are shared by all series of Y values.

Either type will do the job you want here, assuming I've read your question correctly. The major difference between them for this task is that Category charts will place a label on the x axis for every category, provided there is room to do so.

Value axes permit up to 10 steps in the range of values shown on the axis. For a range of values from 0 to 31 (a prime number), this can be a problem. If you want to use a zero value for the left edge of the chart, you will need to use a value other than 31 for the maximum in order to get integer labels along the x axis. In example 2a, I've set the maximum to 35, the number of steps to 7, and used a borderless white rectangle to cover the right end of the chart. For the example, I've set the opacity of the rectangle to 90% to permit you to see what's being covered.

In Example 2b, I reset both minimum and maximum values for the x axis to 1 and 31 respectively. This makes it possible to show integer labels with 2, 3, 5, 6 or 10 steps. Here I've used 10 steps to show a label every three days.

Regards,

Barry

PS: Table and chart done in Numbers '09 (Numbers 2). Numbers 3 may allow more steps in the value axes.

Barry has plotted a Series for each Month of the year.

Jerry

=DAY(A2) is working correctly on the day/month/year date format used in my regional settings.

Ian.

Why would 4/1 be day 4? Or 5/1 be day 5? That doesn't make any sense. Each of those are day 1.

It's interesting to look at date formats used around the world. According to statistics here, over ten times more people use Ian's DMY date format than MDY.

SG

"@Barry: I have no idea how you are doing that. Do you have a spreadsheet I can download?"

Hi 'space,

My bad! Neglected to include the table in my earlier post. These are reconstructions, and have different values, as they are random numbers within a patten in both cases.

The images include the table. In each image one of the charts in selected, and the selection and settings on the table are shown or described.

Line chart: Columns C (Jan) to N (Dec) selected. The chart gets its Data Series labels from Row 1 (a Header row) and its Category labels from Column A (a Header column). Column B is not used for this chart.

Scatter Chart: Data selection includes column B, a non-header column (Date) through column N (Dec). These will initially be treated as data pairs (B-C,D-E, etc.). Click the gear icon at top left and choose Share x values. X values are then take from only column B, and each of the month columns is used as a separate series on the chart. Column A is not used in this chart.

Other details are covered in my previous post.

The 'spreadsheet' is a single table, with values in cells as shown. Month names and day numbers were entered directly. Values in the cells in columns C through I were created with a random number formula, biased with a fixed value and augmented with a second value dependent on the distance in days from midmonth to insure the values followed a discernable pattern. The actual values have no meaning, and wouldn't be of use to you in that regard.

For your case, you would want a summary table to collect the daily figures for each day into a table resembling the one I used. For the benefit of others searching for a method to create such a table, this would be the subject of a new question.

Regards,

Barry

HI 'Space,

=DAY(A2) will work on any cell containing a Date and Time value. "Format" does not change the value in a cell, just the way it is displayed.

Regards,

Barry

Hi 4thSpace,

All cells from A2 down contain the same Date and Time value (the 10th day in the month of February, 2014 at midnight). Midnight is a default or "dummy" value (I entered the values at 9:30 pm). The cell formats have been changed to show various ways to display the value.

Formula in B2 (and Fill Down)

=DAY(A2)

shows that the day of the month is 10 in all formats. As Barry said, the format does not change the value.

Regards,

Ian.

Hi 4thSpace,

In your spreadsheet https://www.dropbox.com/sh/b73uim1h99o17on/DnnYL5skq-, how do I get the x-axis to display days rather than days from January?

I think you are using a Category (LIne) Graph and not a Scatter Plot. Each table in your spreadsheet shows a month with different dates (Categories, fixed spacing on the X axis), rather than Date values (to make a linear scale).

If you want to plot sales by day with each month as a series, you will need to reorganise your data as suggested by Barry.

Regards,

Ian.