Format X axis Date in Excel Chart 1

[bajo71]

Hello,

In the Axis Options window, I have Major Unit set to fixed by Month = 1. What I get is 2/1/09, 3/1/09 etc... What I'd like to see is the last day of prior month, ie 1/31/09, 2/28/09. Is there a property setting I can tweak to accomplish this?

Thanks so much.........Mickey

 

[SkipVought]

Hi,

1. In Chart Options set the x-axis to CATEGORY.

2. In your chart source data, recalculate your date range to have the last day of the month.
[tt]
=Date(Year(YourDate),Month(YourDate),0)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

With a little lost of functionality, you can untick automatic minimum scale and set it to the 31th of any month that has 31 days.

combo

 

[bajo71]

Hi Skip,
Not sure where this is located. I'm new to Excel 2007. I've been going through the ribbons. I selected...

Layout?Axes?Primary Horizontal?MorePrimaryHorizontalOptions

but this brings up the same box I referenced before.

 

[SkipVought]

Did you do STEP 1 FIRST???

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bajo71]

That's what I am trying to locate: Chart Options

 

[SkipVought]

Right click in the chart and select...

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bajo71]

Skip

Ok, in the Layout ribbon to the left I selected Horizontal (Category) Axis. I think this is step one, correct?

Now for the chart source data, I right mouse the chart?Select Data?Choose appropriate Legend Entry and then Edit Horizontal Category Axis Label, then add the formula you reference above?

 

[SkipVought]

Yes, with the appropriate reference.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bajo71]

Thank you much...

 

[bajo71]

One second:

The source data shows 10/31/08; 11/30/08 etc just fine, but chart still shows 11/01/08; 12/01/08. Any idea why? I have indeed relinked the data to the new formula.

 

[bajo71]

Funny thing is, in the Select Data Source window under Horizontal Category Axis Labels, I see the correct formatted dates, but the chart won't post them as such.

 

[SkipVought]

I cannot reconstruct your condition.

I will try in 2007 when I get home -- currently using 2003.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}