problem with pivot table

[RichardO]

Hello all:

I am trying to make a pivot table from my date.

My data includes Names, Dates (which I formatted to month, "m" in the
format cells option)and Amount due.

In designing the pt, I have "Name" in the row field, "Date" in the
columnn field and "Amount due" in the Data field.

I want my data to be grouped as follows:
Month
1 2 3 4
Name $100 $20 $30 $20

Instead I am getting more than 1 month column i.e.

Month
1 1 2 2 2 3
Name $80 $20 $30 $20


Do you know why this is happening? How can change this?

Thanks a lot for your help.

 

[Debra Dalgleish]

Even though you formatted the dates as "m", the pivot table is still
seeing them as individual dates. Click on one of the heading cells, and
in the formula bar you'll see the full date. So, each date gets its own
heading.

You could group the dates by month, and you'll see month names in the
headings. Or, add a column to the source data table, and calculate the
month number, e.g. =MONTH(A2)
Then, add that field to the column area of the pivot table.

 

[Andyroo]

I have the same problem. Just because you have formatted the date t
look right does not mean that Excel will treat it as the month you hav
displayed. I think we both need the same solution - I want Excel t
store the displayed date of Jan 04, as the same and not a date value s
that when i use pivot table I get all the entries for a month as on
line , not multiple lines.
I agree with Debra bt in my case I want the month and year, like Jan 0
and not '1'

Who can help, please ?
;

 

[Debra Dalgleish]

Add a column to the source table, and convert each date to the first of
its month. For example:
=DATE(YEAR(A2),MONTH(A2),1)
Then, format that field as mmm yy, and add it to the pivot table.