How can I make mm/dd/yyyy to sum up as mm/yy??

S

Stinkey Turner

I would like to have my charts sum up daily occurances by the month/yea
in Pivot Charts, etc.

If I have a date, such as 2/2/2005, I can make it appear in the cell a
Feb. 2005 by formatting the cell. But the data on the formula bar stil
diplays 2/2/2005. Because of this, I cannot get it to chart by month.

Is there an easier way than reformatting the date in an adjacent cel
manually?

Thanks!
Mik
 
R

robert111

yes, you can format how the date appears on your chart by highlighting
the relevant axis, then format, number, custom and choose mmm yy
 
R

Ron Rosenfeld

I would like to have my charts sum up daily occurances by the month/year
in Pivot Charts, etc.

If I have a date, such as 2/2/2005, I can make it appear in the cell as
Feb. 2005 by formatting the cell. But the data on the formula bar still
diplays 2/2/2005. Because of this, I cannot get it to chart by month.

Is there an easier way than reformatting the date in an adjacent cell
manually?

Thanks!
Mike
Click to expand...

If you are using Pivot Tables/charts, you can do it easily.

With your cursor in the Date Column, right click and select
Group and Show Detail
Group

Then select either Months; or Months + Years; dependiing on how you want the
results displayed.

This will change the display both in the table and the chart.


--ron
 
S

Stinkey Turner

Hi Ron,
When I do this in pivot table, I only see a list box under show detail
that lists the other available headers from the data sheet. I don't see
an option to show months, or months/years. Am I doing something wrong?

Hi Robert,
You fix makes sense, but for some reason, the "format" tab on the X
axis (horizontal) is not available. Only on the Y. My dates are on X .
Any ideas why this is?
Thanks!
Mike
 
R

Ron Rosenfeld

Hi Ron,
When I do this in pivot table, I only see a list box under show detail
that lists the other available headers from the data sheet. I don't see
an option to show months, or months/years. Am I doing something wrong?
Click to expand...

Clicking on "Show Detail" submenu rather than "Group" submenu?


--ron
 
S

Stinkey Turner

Aha, I tried it in Excel 2003 (at home) and it works like a charm!
Any idea how to do it in 2000?
 
R

Ron Rosenfeld

Aha, I tried it in Excel 2003 (at home) and it works like a charm!
Any idea how to do it in 2000?
Click to expand...

No, I have 2002. I don't know how Pivot Tables were set up in 2000 for this
feature.

In XL97, there was a group button on the Pivot and Query toolbar. Maybe that's
what's present for 2000?


--ron
 
Top