Problem formatting date values in chart...

[Private Person]

I have a blood pressure tracking spreadsheet -- at a minimum, it tracks
Systolic and Diastolic pressure for a given day.

A B C
E.g.: Date Systolic Diastolic
1 9/3/04 120 90
2 9/4/04 123 87
3 9/5/04 129 79


I currently have a simple line graph that shows systolic data points over
diastolic data points.

The left hand (Y axis) value is "Pressure".

My problem is that I want the "date" values of Col. A to display at the
bottom of the graph as a label with each corresponding data point and as
simple as it sounds, I can't seem to do it.

At one point I did manage to get a true "Date" value there, but the date
values were a month out in the past, compromising the visuals of the graph.

So, how do I get the values of Column A (whether as a "Date" value or an
"Asci" representation) to show up under (but "outside" of the graph -- i.e.,
as "labels") the corresponding Col. B and Col. C values?

Thanks,

Marc

 

[Guest]

I am assuming you mean that you want the dates to appear
as labels on the x-axis and that your problem is with the
formatting of these dates.

I don't know why your dates would show up 'a month in the
past' but I will share a few things you can try to solve
your problem:

TRY 1.
.. Right click inside the chart and select 'SOURCE DATA'
.. In the dialogue box that appears, click on the 'SERIES'
tab
.. At the bottom is a box where you can select
your 'Category (x) axis labels' - if you click on the
little red arrow button, it will give you an opportunity
to select the cells that contain your date data.
.. Hit OK

If your dates now look wrong, try one of the following:

TRY 2
.. Select the cells in which your dates appear and format
the cells with the correct date format
[FORMAT/CELLS/NUMBER]

If this does not work try this:

TRY 3
.. Select the COLUMN in which your dates appear and select
[DATA/TEXT TO COLUMNS]
.. In the dialogue box that appears, select 'FIXED WIDTH'
and press NEXT
.. Ignore the next screen and press NEXT
.. On the following screen, you can set the data format to
date, by selecting the box that is labeled 'DATES' and
picking a format from the drop down list.
.. Hit ok

DONT FORGET TO REFRESH YOUR GRAPH (sometimes it is set on
manual update)


If this doesn't solve the problem, I don't have a clue how
to help.

Sorry.

 

[Jon Peltier]

In addition to these anonymous suggestions, you could improve the chances of getting
a proper chart first try if you clear the cell that now says "Date". Having a blank
cell lets Excel know you want to use the first row for series names and the first
column for category labels. Otherwise Excel will make a wild guess.

Also, even telling Excel you want to format a set of apparent numbers as numbers
does not assure that it happens; sometimes once something is identified as text,
it's hard to change. Here's how to convert them. Copy a blank cell, select the
textual numbers (dates are numbers too), and on the Edit menu, choose Paste Special,
with options Value and Operation - Add. When told to add these cells, Excel guesses
that the blank is a zero and reinterprets the date text as numbers in any format
that it can recognize, in this case a date.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

I am assuming you mean that you want the dates to appear
as labels on the x-axis and that your problem is with the
formatting of these dates.

I don't know why your dates would show up 'a month in the
past' but I will share a few things you can try to solve
your problem:

TRY 1.
.. Right click inside the chart and select 'SOURCE DATA'
.. In the dialogue box that appears, click on the 'SERIES'
tab
.. At the bottom is a box where you can select
your 'Category (x) axis labels' - if you click on the
little red arrow button, it will give you an opportunity
to select the cells that contain your date data.
.. Hit OK

If your dates now look wrong, try one of the following:

TRY 2
.. Select the cells in which your dates appear and format
the cells with the correct date format
[FORMAT/CELLS/NUMBER]

If this does not work try this:

TRY 3
.. Select the COLUMN in which your dates appear and select
[DATA/TEXT TO COLUMNS]
.. In the dialogue box that appears, select 'FIXED WIDTH'
and press NEXT
.. Ignore the next screen and press NEXT
.. On the following screen, you can set the data format to
date, by selecting the box that is labeled 'DATES' and
picking a format from the drop down list.
.. Hit ok

DONT FORGET TO REFRESH YOUR GRAPH (sometimes it is set on
manual update)


If this doesn't solve the problem, I don't have a clue how
to help.

Sorry.

-----Original Message-----

I have a blood pressure tracking spreadsheet -- at a

minimum, it tracks

Systolic and Diastolic pressure for a given day.

A B C
E.g.: Date Systolic Diastolic
1 9/3/04 120 90
2 9/4/04 123 87
3 9/5/04 129 79


I currently have a simple line graph that shows systolic

data points over

diastolic data points.

The left hand (Y axis) value is "Pressure".

My problem is that I want the "date" values of Col. A to

display at the

bottom of the graph as a label with each corresponding

data point and as

simple as it sounds, I can't seem to do it.

At one point I did manage to get a true "Date" value

there, but the date

values were a month out in the past, compromising the

visuals of the graph.

So, how do I get the values of Column A (whether as

a "Date" value or an

"Asci" representation) to show up under (but "outside" of

the graph -- i.e.,

as "labels") the corresponding Col. B and Col. C values?

Thanks,

Marc





.

 

[Private Person]

None of these suggestions appear to have helped, although it may be operator
error.

The "Date" column values are all formatted to MM/DD/YY and they correspond
one-to-one with the two data points plotted on the graph, so that each
"Date" value has only one Diastolic and one Systolic value -- there's no
tricky data here.

When I try selecting 'SOURCE DATA', clicking on the 'SERIES' tab, selecting
'Category (x) axis labels' and select the "Date" cells under my date column
(with or without a "blank column A"), I end up with a straight line leading
to a cluster of data at the end for both pressure values (and no "dates"
under the graph as labels for each Systolic and Diastolic pressure point
pair).

 

[Jon Peltier]

Do you mean blank column A or blank cell A1? Where does the line start, that ends in
a cluster of points? What's on the category axis, anyway? What does the series
formula say?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Private Person]

Do you mean blank column A or blank cell A1? Where does the line start, that ends in
a cluster of points? What's on the category axis, anyway? What does the series
formula say?

I tried it as my spreadsheet existed (Column A holds my "Date" dates) and
with an inserted column w/o data, Column A (so that then my "Date" dates are
in Column B).

Selecting the Catagory X Axis Labels and highlighting the valid "Date" dates
in Column A produces a formula of "=Sheet1!$A$3:$A$37".

In it's simplest form, the first row of data contains "9/3/04" as a Date
Type Formatted MM/DD/YY, the second row contains 143 as a Numeric, and the
third row contains 76 as a numeric. The second row is 9/4/04, 151, 83. Third
row 9/5/04, 163, 92, etc. with entries for every day of Sept.

The Left Side Y Axis is labeled "Pressure" with Excel-generated values
starting at Zero and going to 160 in increments of 20 with associated lines
runing across the graph . Excel is providing it's own calculated gap between
data points. The bottom is labeled "By Day". This is where I would like the
actual Date dates to appear instead, one date per pressure value pair.

Normally, the Chart contains a graph with two horizontal lines of plotted
data. The top line is Systolic data, the bottom Diastolic. So for the first
row of data, I would have a data point indicater ("Value") at 143 for the
top (Systolic) graphed line and a DPI at 76 for the bottom (Diastolic)
graphiced line. Then gap, data point top line at 151, bottom line at 83,
gap, DP top line at 163, bottom line 92, etc.

If I select Catagory X Axis Labels, select the dates in Column A, then the
graph shows up with two graphed lines, the top one starting with a data
point from the 4th Row (Series "Systolic", Point "9/6/04" Value "141") then
a straight line (Series "Systolic" Point "9/3/04" w/no value) to the end of
the graph with a blob of all of the remaining data points at the end of the
line (however, the first point of the blob is Series "Systolic" Point
"9/3/04" Value "143". The same for the bottom Diastolic line (except,
obviously, with the Diastolic data). No "Date" values appear as labels for
the X axis.

 

[Jon Peltier]

I still suspect your data, and nothing you described disproves the hypothesis. When
you select the cell that has "9/3/04", what does the formula bar show? If it doesn't
show 9/3/2004 (a 4-digit year), your dates are not dates.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

Private Person sent a copy of his workbook, and I discovered the problem. His data
were true dates, between 9/3/04 and 10/7/04, except for the anomalous 9/6/02. So
this point appeared all by itself at the beginning of the chart, and all the others
were two years later, at the end of the chart. I suspected the data, but for the
wrong reason.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______