how can I change the default month names for dates grouping in pivot tables

**bagullo** · 03-22-2011, 07:09 AM

hello there,

I have a data set with dates that I chart grouping by month and year
however it sets month names in "mmm" format, and in the default language (spanish) for my computer. Is there a way to change that to month number (1,2,3..), or to english abreviations at least, so that the chart is more international-oriented?

I tried changing format of everything, from axes to field settings to cell format... nothing worked

thanks a lot!

**teylyn** · 03-22-2011, 07:26 AM

Have you tried formatting the dates with custom format

m

? That will give you just the month number.

**bagullo** · 03-22-2011, 08:46 AM

Originally Posted by teylyn

Have you tried formatting the dates with custom format

m

? That will give you just the month number.

the thing is that I need them in date format so that I can group by month and year using the pivot table options

not only to group by month, but also to show items with no data

if the dates are in "m" format I cannot do that anymore

**teylyn** · 03-23-2011, 07:12 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**bagullo** · 03-24-2011, 06:10 AM

Originally Posted by teylyn

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I thought I had uploaded my file just to see now that I didn't succeed...
anyway here it comes!

thanks in advance

**teylyn** · 03-25-2011, 03:31 AM

Hello,

When you use the grouping by Year and month, Excel seems to automatically spell out the month in text format. I have not found a way to display the month as a number.

You will need to include another column in the pivot table source that calulates the month number (see attached file and the pivot table in sheet1)

You can then group by year and month, include the month field in the pivot table and then delete the column with the text month values.

cheers,

**bagullo** · 03-25-2011, 05:24 AM

Originally Posted by teylyn

Hello,

When you use the grouping by Year and month, Excel seems to automatically spell out the month in text format. I have not found a way to display the month as a number.

You will need to include another column in the pivot table source that calulates the month number (see attached file and the pivot table in sheet1)

You can then group by year and month, include the month field in the pivot table and then delete the column with the text month values.

cheers,

Hi teylyn,

thank you for your effort!
In the example your approach seems to work fine changing the field setting of "Month" to show items with no data, and I am sure that it would work in 99% of time . The only concern I have is that if there is no items for a certain month of all years then that month will not display in the chart --- am I right?

I just thought of another possibility, which is your approach complemented with 12 dummy lines (one for each month) with a 0 number in de docs columns. That would force the pivot table to display the field no matter what, without affecting the data.

regards!