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

 

[mck1dad]

http://www.box.net/shared/rma8usbmd4

here you go

I really appreciate your hanging with me on this. I has got to be something simple I am just overlooking.

 

[p45cal]

The reason is that there are blank cells in the Scheduled date column (and maybe those blanks having the General format might also screw things up).
Solutions:
1. Sort the source data by scheduled date and only select that part with valid dates in that column for inclusion in the Pivot Table.
2. Format the blank cells in that column in the same way as those cells with dates in, then put some dummy data in there - something which can't be confused with real data. I put a 1 in them representing 1 Jan 1900. Then you'll be able to group by month - you'll probably want to group by year too, as all Januaries will be lumped together. Then in the Pivot Table you can filter out that dummy year/date.
See here, the section titled "Problems when Grouping Data" here, and here.

I'll briefly put a file up with a sheet3 with it done. (This was done in xl2010 in compatiblity mode, so I hope it still works. The following embedded link will die tomorrow.

ps. I'm guessing the fomat actually matters much, as long as they're all excel dates, it should group properly.
<embed src="http://www.box.net/embed/ex1rgdtr5xa9y3m.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

 

[mck1dad]

like I said must be something simple and it was the blanks issue screwing it all up.

I really appreciate you hanging with me on this.

Thanks for your help!