VBA Charting (Date Issue)

99bobster99

Board Regular
Joined
Jul 19, 2004
Messages
95
Hello,

I am using "ChSeries" and "ChChart" within my VBA code and I am having an issue displaying the "X" axis labels. The X axis data points are dates, with a time component. Here is the structure;

"X" Axis label "Y" Axis Data
[2008-02-27 09:49] [1.2650]
[2008-02-27 09:50] [1.2600]
[2008-02-27 09:51] [1.2600]
[2008-02-27 09:52] [1.2601]
[2008-02-27 09:53] [1.2700]
[2008-02-27 09:55] [1.2700]
[2008-02-27 09:57] [1.2601]
[2008-02-27 09:58] [1.2650]
[2008-02-27 09:59] [1.2600]
[2008-02-27 10:01] [1.2600]

I keep getting only the decimal value of the date to the 10 decimal point, being displayed on the "X" axis label. How can I get the date and the corresponding time to be displayed instead?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
If you have True Serial Date/Time in your X Axis column, then you should be able to format the x axis as Date and Time. Right click the axis on your chart, Choose Format Axis...
In the Number Tab, choose the desired Format.
 
Upvote 0
Hi Datsmart,

Thank you for your reply.

I tried what you suggested, but my X axis selection for Format has the entry window greyed out? My chart does not show any values on the X axis either, in this configuration window? I've been looking through the internet and finding a few people that had this same condition, but no resolutions?
 
Upvote 0
A weird thing this charting .... can someone help me understand why this command;

.Axes(chAxisPositionLeft).NumberFormat = "mm/dd/yy hh:mm"


yet, this command, does not;


.Axes(chAxisPositionRight).NumberFormat = "mm/dd/yy hh:mm"


My "NumberFormat" for my X Axis is greyed out, how is this override changed?
 
Upvote 0
My guess for your "NumberFormat for your X Axis being greyed out" is that you have Text instead of Numbers.

In my earlier post, I mentioned, "If you have True Serial Date/Time in your X Axis column,"
You can check this by putting a formula similar to this on your sheet:
Code:

=ISNUMBER(A1)

Where A1 is one of your Date/Time cells. If you get FALSE, it is text.
 
Last edited:
Upvote 0
Hi Datsmart,

This is the weird thing, all of my cells in Column "A", are numbers?? The "ISNUMBER(A1)" test comes up with "True". ?? I am using the latest Microsoft Chart Tools, ver 11. This uses CHChart and CHSeries?

I don't understand why these commands;

.Axes(chAxisPositionRight).NumberFormat = "mm/dd/yy hh:mm"
.Axes(0).NumberFormat = "mm/dd/yy hh:mm"

throw errors?? This is probably due to the fact that my "Number Format" selection is greyed out for the (0) axis??

Any ideas?
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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