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
Last edited by jaywizz; 10-07-2009 at 12:28 PM.
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...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That works for me
Thanks for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks