Problems with changing date format

Hi. I've changed the column's F format so that in each cell in the column the date appears like March-2014 rather than (originally) 19/03/2014. So far so good. However, when using pivot table column's F dates are still shown in the old format (19/03/2014), and so I cannot sort dates by month.. Any idea how to fix it through vba?

Please Login or Register  to view this content.

you can group dates by month/year in a pivot table

Hi Josie, much appreciate getting further guidance, where to look at in the pivot menu?

you need to add the date field to the row or column field area then just right click and choose group :-)

It may work, but the problem is that date (month), in my case, must be the filter so it cannot be row/column .

If to get back to my original question, I wonder how instead of just changing the format I'll be able really changing 19/03/2014 to March 2014 (as an example).

after you group you will have additional date fields (eg for year and month) and you can move them to the report filter area

Still a bit confused, maybe adding a sample of my work may help, could you demonstrate how to do such grouping?

The sample contains two months - Dec-12 and Jan13

Thank you

I don't see how that will help you because you won't be able to reproduce it in your real workbook if you can't follow what I said? an easier way would be to insert a new column and use a formula like:
=TEXT(D2,"mmm-yy")
to convert the dates to text values and then use that in the pivot table

Ok, thanks for assisting, revised code below. Still getting error, maybe TEXT is a worksheetfunction, isn't it?

Please Login or Register  to view this content.

yeah-I did say formula ;-)

Please Login or Register  to view this content.

Oh, I missed it. Thanks, works smoothly now

Off topic: have you noticed the inherent negative incentive to help we have here? I've just been blocked from adding reputation to Josie, just because I did so a few hours ago when she had helped me on different problem I had...