Problem with sorting by date

I have a column showing date DD/MM/YYYY

I want to be able to get a new column stripping out the days so that I can chart changes over a month rather than each day of the last 2 years.

I have tried altering the cell format to MM-YYY although this shows the format I want when i run a pivot chart it will still create records for individual days.

I then tried copying and pasting the above as text only. This results in the pivot chart sorting A-Z rather than by date.

Any suggestions?

Thanks

Try formula:

=Date(Year(A2),Month(A2),1)

where A2 contains first date to convert.. copied down.

so that all dates use the 1st of the month.. is always referenced...

Does that help...