Nikko2

How do I do this???

For eg.

I currently have dates displayed as 26-Jul-02
When I format cells to show as Jul-02 it appears fine.

However when I use the data in a pivot table it shows as 26-Jul-02 again.

I understand how the date function works. But I cannot find out how to get excel to accept that I only want month
and year info in my pivot table.

Does any one know what I need to do.

All help greatly appreciated.


Nick

Taff

Just format the cells in your pivot table the same way you would any other cells

Nikko2

That works sort of.

But unfortunatley I end up with lots of columns in the pivot table for march july etc. I only want one of each month. That is why I was trying to remove the day component.

I just want excel to ignore the day and not the month or year. When you format for mmm-yy it appears to have done it. But it is still remembering the day secretly this ***** up my pivot table and gives me hundreds of columns instead of 12.

MattN

Ok, firstly, chnaging how something looks (format) doesn't mean it is no longer there.

So changing the format won't help, besides the way dates are used in excel if you just used mmm-yy it would still have a day as 01.

Why not simply have another column in the table with the month and use this column in the pivot table?

MattN

oh just in case you don't know how to do it...

=MONTH(cell with date in it)

That will return the number of the month

then use this formular in the next column to change that to a month in words

=INDEX($G$7:$H$18,E2,2)

G7:H18 is a table of 1-12 on the left and Jan to Dec on the right and E2 is the cell with the result from MONTH

ThePosh

OK,

Create a field with the following =Month(Cell with date in it)&"/"&Year(Cell with date in it). Then pivot on that.

Or you can use groups in your pivot table I think...

1 Select a single date or time item in the field you want to group.
2 On the PivotTable toolbar, click Group .
3 If necessary, type the first item to group in the Starting at box, and type the last item to group in the Ending at box.
4 In the By box, click one or more time periods for the items in the group.

If you clicked only Days in the By box, enter the size of the group in the Number of days box. For example, type 7 in the Number of days box to group items by weeks.

Not sure if you can split by month and year though.

Cheers,

Posh

Nikko2

Posh you are a true excel wizard that first bit has done it spot on.


Cheers

Nick

Nikko2

Edited to say I realised I had tried something similiar to that but I need it to have the month and year on.
As the pivot table runs over a couple of years.

If there was an =MONTHANDYEAR(D3)command that would do but there doesnt seem to be such a thing.

Thanks for trying.

Nick



[Edited by Nikko2 - 4/5/2003 10:23:12 AM]