PivotTables and dates

[cjackson]

I am just starting to use PivotTables in EXCEL and on the most part find them REALLY POWERFUL however I have ran into a little problem displaying dates.

I would like my row headings to be Month Year (e.g. Mar 99) but can not seem to achieve this. Regardless of the way I format the date field in my source data I always end up Individual days as my row headings.

Can anyone suggest what I am missing?

INCIDENTALLY if I format the column as Mar 99, copy it, paste it into notepad, copy it from notepad and paste it back into EXCEL it works?

 

[StickMan]

sounds like you just want to enter the date as text for the heading. just add an apostrophe before the date.

Example, type in the cell: 'Mar 99

This will return a value of Mar 99, not 3/1/99.
You can find more info in the Help, look for keyboard shortcut_menu

NOTE: not sure if this is still around in Excel but make sure the Lotus emulation is turned off in the options. In Lotus, the apostrophe was a left align.

 

[sfarr]

In Office 97, this was true with Excel pivottables (reformatting the date field to text to have column heading appear MMM-YY). Please note that in Office 2000 this is not the case.

 

[renrut]

When you are in the Pivot Table layout wizard and double click the date field and then select number (bottom right set of bullets) you should be able to format the date as you would like it to appear.

 

[cjackson]

Thanks for the answers everyone,

I think I was being a bit thick, but now its working OK so I'm fine until next time…

I also needed to play around with the grouping levels a little to get exactly what I wanted.

Thanks again.

Chris