Date axis on Graph automatically chooses 00-Jan-00 as minimum value

[PeterWadeZa]

Greetings good people,

I am using Microsoft Excel 2010.

I plot an array of numbers versus dates.

The minimum of the x-axis range is automatically chosen by Excel as "00-Jan-00" instead of the minimum date value in the array that I specify.

I imagine this is not an issue but the date values are ordered from earliest to latest.

The date values are correct - they display properly when I choose General for formatting (I get the Real value corresponding to the Excel date convention) and when I choose the Custom Format dd-mmm-yy,

Is there a solution to this problem other than my manually determining the minimum date and manually setting the axis minimum to about that date?

All the best, Peter

 

[Robert Mika]

Greetings good people,

I am using Microsoft Excel 2010.

I plot an array of numbers versus dates.

The minimum of the x-axis range is automatically chosen by Excel as "00-Jan-00" instead of the minimum date value in the array that I specify.

I imagine this is not an issue but the date values are ordered from earliest to latest.

The date values are correct - they display properly when I choose General for formatting (I get the Real value corresponding to the Excel date convention) and when I choose the Custom Format dd-mmm-yy,

Is there a solution to this problem other than my manually determining the minimum date and manually setting the axis minimum to about that date?

All the best, Peter

Hello and wecome to Mr Excel
Did you try to use Text Axis?
Otherwise can you post your sample data?

 

[PeterWadeZa]

Hello and wecome to Mr Excel
Did you try to use Text Axis?
Otherwise can you post your sample data?

Hello Robert, here is my sample data. I simply pasted it into this document. I am very new to MrExcel and I don't know if another way of displaying my data is required/preferred. I would be grateful for advice.

I plot pH_Diss_Water against Date.

Date	DMS_Tot_Water	EC_Phys_Water	pH_Diss_Water
28-Jan-99	2932.968	390	7.1
01-Oct-99	2621.715	392	6.6
19-Oct-99	2824.213	442	7.9
22-May-02	3327.459	462	6.8
22-May-02	501.029	82	7.4
26-Jun-02	2419.061	380	8.1
26-Jun-02	994.027	151	8
26-Jun-02	3144.751	393	6.8
29-Jul-02	2260.053	360	8
29-Jul-02	2984.044	446	6.8
02-Oct-02	3609.414	495	8.1
02-Oct-02	4689.651	710	7.2
02-Oct-02	2498.126	373	7.7
02-Oct-02	3423.703	436	7
02-Oct-02	3126.019	423	7.9

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

All the best to you and thank you for your interest in my problem.
Peter

 

[PeterWadeZa]

Hello Robert, here is my sample data. I simply pasted it into this document. I am very new to MrExcel and I don't know if another way of displaying my data is required/preferred. I would be grateful for advice.

I plot pH_Diss_Water against Date.

Date	DMS_Tot_Water	EC_Phys_Water	pH_Diss_Water
28-Jan-99	2932.968	390	7.1
01-Oct-99	2621.715	392	6.6
19-Oct-99	2824.213	442	7.9
22-May-02	3327.459	462	6.8
22-May-02	501.029	82	7.4
26-Jun-02	2419.061	380	8.1
26-Jun-02	994.027	151	8
26-Jun-02	3144.751	393	6.8
29-Jul-02	2260.053	360	8
29-Jul-02	2984.044	446	6.8
02-Oct-02	3609.414	495	8.1
02-Oct-02	4689.651	710	7.2
02-Oct-02	2498.126	373	7.7
02-Oct-02	3423.703	436	7
02-Oct-02	3126.019	423	7.9

<tbody>
</tbody>

All the best to you and thank you for your interest in my problem.
Peter

This problem goes away if I select the entire block and make a scatterplot. If I select the Date column and the pH column the problem appears - the x-axis origin set to 1900. I delete the graph and select the entire block again, make a scatterplot and now the problem appears and doesn't disappear.

 

[Robert Mika]

This problem goes away if I select the entire block and make a scatterplot. If I select the Date column and the pH column the problem appears - the x-axis origin set to 1900. I delete the graph and select the entire block again, make a scatterplot and now the problem appears and doesn't disappear.

Is scatterplot your default graph or you choose them only in the last example?
Scatter needs two values to plot-not dates and values.
In your case it maybe be that Excel threat the Dates as values but show them as Dates format.
See below:
http://dl.dropbox.com/u/78583609/CHART.xlsx

 

[PeterWadeZa]

Is scatterplot your default graph or you choose them only in the last example?
Scatter needs two values to plot-not dates and values.
In your case it maybe be that Excel threat the Dates as values but show them as Dates format.
See below:
http://dl.dropbox.com/u/78583609/CHART.xlsx

Hello Robert,

Thank you for your kind attention.

The spreadsheet you sent me - I cannot reproduce my error. Everything faithfully works as I would expect it to.

The spreadsheet where I encounter the difficulty is a many-sheet spreadsheet and it was created in the previous version of Excel, and converted to the most recent version.

It is entirely likely that a bug crept in during the conversion process.

I think my problem is solved.

Thank you very much.

All the best, Peter

 

[Robert Mika]

Hello Robert,

Thank you for your kind attention.

The spreadsheet you sent me - I cannot reproduce my error. Everything faithfully works as I would expect it to.

The spreadsheet where I encounter the difficulty is a many-sheet spreadsheet and it was created in the previous version of Excel, and converted to the most recent version.

It is entirely likely that a bug crept in during the conversion process.

I think my problem is solved.

Thank you very much.

All the best, Peter

You are welcome.
Glad it works for you.