Date not working in Pivot table

Hi,

I have data that I have pulled out of an external source which includes dates on which sales are made in a particular month. I want to convert that date to show the month and year only for pivot table purposes, for if I access the raw data number and format it, when I pivot it, it will show the actual date rather than the month and year.

As an example, this is how I currently need to work around it. The data set has in one column a date of 15/08/2010. I want the data in the pivot table to show only Aug 2010 when I reference this column. If I format the actual data column with 15/08/2010 with mm-yyyy it does not work when it is extracted into the Pivot and still shows the original date of 15/08/2010.

To work around this at the moment I have to do a separate 'month of year' reference on another sheet, to get in this case '8' for August, & also split out '2010' for year, then set up another column which will convert '8' into the month of August and will combine with '2010' to show 'Aug 2010' in a separate column referencing the line of data. I then reference this new column in my pivot to get the required 'Aug 2010' to show.

This is a long winded way of converting the date and was hoping there might be an easier way to do it so that I don't need to go through these multiple conversions to get the right format to show in the pivot table?

Thanks in advance for your help

Regards

Bill

There isn't a easy way to format the date 15/08/2010 as a date format because it not recognized by excel as a date.15/08/2010 is treated as text because it is aligned to the cell on the left. You can tell if the cell is truly formatted as a date when you press the ctrl + ~ button when you see the actual date serial code.

Resorting to manual extraction and manipulation of the original data set is the way to go.

Thanks guys, I think I am stuck with this one. I did try to use the date format change in the Pivot however it seems you cannot change the number format when the date relevant to the field with the date in the 15/08/2010 format is selected. The challenge continues on!

Hi, apologies for the delay in response however have been away unexpectedly. I tried the group function and did not work. I am not in the office at present however will access a part of the pivot table and upload as requested

Regards

Bill

Hi,

I have attached an example of the pivot I am having problems with. The dates are for example 1/8/2010, 2/8/2010 etc. When the data is put into a pivottable, it shows each date split out when all I want is a total for July 10, August 10 etc. Have not been able to find a solution to this one!

Appreciate anyone'e help!

Cheers

Bill

On your data sheet you need to format column A as DATE.
Then jump to your pivot table and click on the Group Selection.
This will allow you to group your dates by month.

Find attached.