change dd/mm/yyyy to mm/yyy

[mck1dad]

I do not just want to do a format change because my pivot table still sees the day and brings all the dates into the pivot table.

I want the pivot table to group by month sort by month and count by months and not the specific days in the month

thanks

 

[p45cal]

Pivot tables do this for you; right-click a date in the pivot table and choose Group…, then select months and OK.

 

[mck1dad]

I tried that and I get a message "cannot group that section"

 

[Desu Nota from Columbus]

I'm just throwing an idea out, but my excel doesn't have the dd/mm/yyyy format so it might be seeing the dd as the month.

Try copying/pasting your data to another sheet and changing the format to 14-Mar-2001 and testing it again. See if the pivot table can then group by month.

 

[p45cal]

can you post the file up on the interweb? say box.net of skydrive or some such?

 

[mck1dad]

http://wikisend.com/download/152668/I changed this format in my original file to May.docx

wikisend ok

 

[p45cal]

Are you starting the process by clicking on a date? As in (xl2010):
<embed src="http://www.box.net/embed/ygxojn6ufj8244s.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

 

[mck1dad]

http://www.box.net/shared/9vo63tv9j1

First on the data sheet I refromated col z to be May-10 not 5/12/2010

However you can see in the forula bar the date is stil behind the format.

When I put into a pivot the pivot takes the date not the month/yr and will not allow me to group it either

 

[p45cal]

I'm beginnig to suspect that these dates aren't dates at all, but strings looking like dates.
If you right click a date in the source table and choose format cells…, what category does it show as?

It would be a lot easier if you could post a version of the file itself rather than images - sanitised if necessary to protect whatever, but still showing the problem.

 

[mck1dad]

sent you a posting of the file earlier on wikisend.com
i'll try on box.net