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

P

PeterWadeZa

New Member
Joined
Oct 13, 2012
Messages
4
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
PeterWadeZa said:
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
Click to expand...

Hello and wecome to Mr Excel
Did you try to use Text Axis?
Otherwise can you post your sample data?
 
Upvote 0
Robert Mika said:
Hello and wecome to Mr Excel
Did you try to use Text Axis?
Otherwise can you post your sample data?
Click to expand...

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.

[TABLE="width: 425"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]DMS_Tot_Water[/TD]
[TD]EC_Phys_Water[/TD]
[TD]pH_Diss_Water[/TD]
[/TR]
[TR]
[TD="align: right"]28-Jan-99[/TD]
[TD="align: right"]2932.968[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]7.1[/TD]
[/TR]
[TR]
[TD="align: right"]01-Oct-99[/TD]
[TD="align: right"]2621.715[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]6.6[/TD]
[/TR]
[TR]
[TD="align: right"]19-Oct-99[/TD]
[TD="align: right"]2824.213[/TD]
[TD="align: right"]442[/TD]
[TD="align: right"]7.9[/TD]
[/TR]
[TR]
[TD="align: right"]22-May-02[/TD]
[TD="align: right"]3327.459[/TD]
[TD="align: right"]462[/TD]
[TD="align: right"]6.8[/TD]
[/TR]
[TR]
[TD="align: right"]22-May-02[/TD]
[TD="align: right"]501.029[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]7.4[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jun-02[/TD]
[TD="align: right"]2419.061[/TD]
[TD="align: right"]380[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jun-02[/TD]
[TD="align: right"]994.027[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jun-02[/TD]
[TD="align: right"]3144.751[/TD]
[TD="align: right"]393[/TD]
[TD="align: right"]6.8[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jul-02[/TD]
[TD="align: right"]2260.053[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jul-02[/TD]
[TD="align: right"]2984.044[/TD]
[TD="align: right"]446[/TD]
[TD="align: right"]6.8[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]3609.414[/TD]
[TD="align: right"]495[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]4689.651[/TD]
[TD="align: right"]710[/TD]
[TD="align: right"]7.2[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]2498.126[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]7.7[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]3423.703[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]3126.019[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]7.9[/TD]
[/TR]
</tbody>[/TABLE]

All the best to you and thank you for your interest in my problem.
Peter
 
Upvote 0
PeterWadeZa said:
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.

[TABLE="width: 425"]
<tbody>[TR]
[TD]Date[/TD]
[TD]DMS_Tot_Water[/TD]
[TD]EC_Phys_Water[/TD]
[TD]pH_Diss_Water[/TD]
[/TR]
[TR]
[TD="align: right"]28-Jan-99[/TD]
[TD="align: right"]2932.968[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]7.1[/TD]
[/TR]
[TR]
[TD="align: right"]01-Oct-99[/TD]
[TD="align: right"]2621.715[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]6.6[/TD]
[/TR]
[TR]
[TD="align: right"]19-Oct-99[/TD]
[TD="align: right"]2824.213[/TD]
[TD="align: right"]442[/TD]
[TD="align: right"]7.9[/TD]
[/TR]
[TR]
[TD="align: right"]22-May-02[/TD]
[TD="align: right"]3327.459[/TD]
[TD="align: right"]462[/TD]
[TD="align: right"]6.8[/TD]
[/TR]
[TR]
[TD="align: right"]22-May-02[/TD]
[TD="align: right"]501.029[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]7.4[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jun-02[/TD]
[TD="align: right"]2419.061[/TD]
[TD="align: right"]380[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jun-02[/TD]
[TD="align: right"]994.027[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jun-02[/TD]
[TD="align: right"]3144.751[/TD]
[TD="align: right"]393[/TD]
[TD="align: right"]6.8[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jul-02[/TD]
[TD="align: right"]2260.053[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jul-02[/TD]
[TD="align: right"]2984.044[/TD]
[TD="align: right"]446[/TD]
[TD="align: right"]6.8[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]3609.414[/TD]
[TD="align: right"]495[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]4689.651[/TD]
[TD="align: right"]710[/TD]
[TD="align: right"]7.2[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]2498.126[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]7.7[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]3423.703[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-02[/TD]
[TD="align: right"]3126.019[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]7.9[/TD]
[/TR]
</tbody>[/TABLE]

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

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.
 
Upvote 0
PeterWadeZa said:
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.
Click to expand...

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
 
Upvote 0
Robert Mika said:
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
Click to expand...

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
 
Upvote 0
PeterWadeZa said:
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
Click to expand...

You are welcome.
Glad it works for you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
X-Axis Text Field Dynamic Table Challenge
Replies
1
Views
277
awoohaw
awoohaw
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
349
dilshod_k
D
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
T
  • Solved
VBA - Problem with Coding controls for values based on Dates
Replies
3
Views
789
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top