Dates ( UK Formats )

[Paul C Merritt]

I have a column of dates as below :

04-Oct-03
22-Oct-03
01-Aug-02
19-Aug-02
02-Sep-02
02-Sep-02

I want the dates within a pivot table to be grouped by
months eg Oct 03, Aug 02 etc. I have tried to format them
as month and year but when in Pivot table it treats them
as the exact date.

Any ideas ?

Thanks

Paul C Merritt
Notts
UK

 

[Andy B]

Paul

I used to have this problem!! Formatting the cells does not alter how Excel
sees them. One way round it is yo use a helper column with something like

=TEXT(A2,"mm/yy")

in it. This places the text value of the date into the cell. Once this has
been done, and filled down, use Copy, Paste Special, Values to fix it.

Andy.

 

[Paul]

I have a column of dates as below :

04-Oct-03
22-Oct-03
01-Aug-02
19-Aug-02
02-Sep-02
02-Sep-02

I want the dates within a pivot table to be grouped by
months eg Oct 03, Aug 02 etc. I have tried to format them
as month and year but when in Pivot table it treats them
as the exact date.

Formatting has no effect on the values. What you need to do is to make the
pivot table and then group by year and month within the pivot table. See
"grouping, in PivotTables" in Help for details.

 

[Paul C Merritt]

Many Thanks it worked.........