View Full Version : Solved: Help: Time Scale for chart

11-27-2007, 01:33 AM
Hi All,

I have built this template for months ago. The only limited that I found is that the x axis. The scale is not real. But I can not find out how to fix this.

More in details:

1. I draw event by time
2. The range of chart can change.
3. The current x axis has stick mark based on range of 30 days from the first day of range.

==> I need the stick-mark mark the first day of each month, not by range 30 days as current.
(drag the firt roll bar to see, pay attention on the stick-mark of x-axis)
Please help

Thanks in advance

Andy Pope
11-27-2007, 05:06 AM

The simplest way is to add another series to your scatter chart.
The x values are the dates for the first of each month.
The y values are zero for all data points.

Format the series to use a + marker.
Display data labels using X value. Change Number format to display required month & year.

Remove existing X axis labels and tick marks.

11-27-2007, 09:50 AM
Thanks Andy for prompt feedback.

Further more, is there any way format related? I mean no additional draw, calculation...

You can see, the chart is re-calculated consecutively, my macro already too heavy...

thanks again

Andy Pope
11-28-2007, 04:40 AM
See attached.
I have added a data series that plots the 1st of each month.

11-28-2007, 07:42 AM
Hi Andy,

You are great. But it sounds that I have not explained clearly about what I mean.

Please, kindly go through these steps to get what I stuck in:

1. Open the file you sent me
2. Drag the first roll-bar to left-end (or right-end)
3. You will note the X-axis will lack of some months mark.

WHy this is important? :

Because: I'm trying to make this as a tool to tracking event. When ever you have a news, just add the information in the bottom cells (yellow ones) and then, this tool will auto put your news on the chart.

So, the x-axis will be changed consecutively everytime there is a change in:
-News added
-The view time changed (by dragging either the two rolling bar)

Thanks again Andy

Andy Pope
11-28-2007, 08:03 AM
You just need to add more points to cover the periods expected.
Change the first date to 1/jan/2006 and the increment by month for say 100 rows.
Change the data series to use this range.

You will now have enough dates to last until April 2014

11-28-2007, 09:41 AM
HI Andy,

Actually, I have used (date()) to calculate that column (after your file)

But you're right, No need for that complexity, 100 rows is enough.

Thanks again. You are so supportive.