Excel 2007 Pivot Table - Can't Group On Dates

Steve1944

New Member
Joined
Jul 5, 2012
Messages
9
I have a stripped down data source for debugging purposes
I only have 4 rows of data for test purposes. The dates are formatted as dates

When I create the pivot table the dates become my column values.
When I select the first date in the pivot table the Group By Field menu option is grayed out.
I tried setting a tabular format but didn't work.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Are the dates left aligned or right aligned in the cells if you don't manually apply any alignment? Also, did you specify the data source for the pivot table as just those rows, or did you use the entire columns?
 
Upvote 0
Thanks for responding. It is very confusing. When I look at the source data the dates are left aligned. If I change the format to number they change to a number. I change them back to date. and then insert the pivot table, using only the few rows I included for test purposes. Then I looked at the format of the dates in the pivot table. They are 'general'. The formatting didn't get carried over. I then can still not 'Group on field' - it's grayed out.

I then realized that the name of the column in the source data was 'Date'. I thought this mught cause some sort of keyword conflict so I changed it to Transacrion Date. And recreated the pivot table. I noticed that the field name didn't change!. I then pasted the source data into a new workbook, rettried everything, and it now worked. I'm not sure why. Might it have to do with the pivot cache needing to be cleared after I reformatted?
 
Upvote 0
I now see what the problem is but not sure how to fix it. It is in the source data. If I select the dates and try reformat as a number, the Sample in the dialog box stays as a date and I can't reformat as a number. The dates stay left aligned and don't change to integers. For some reason I can't reformat to date. They look like dates but are not dates and I can't seem to change them to dates.
 
Upvote 0
Select the data. Choose Data-Text to columns, Delimited, clear all the delimiters and in the last step of the dialog specify that it's a date field and whichever DMY order it is, then press Finish. That should convert them to true dates.
 
Upvote 0
Solved!. I selected the Transaction Date column and went to the Data tab, then did Text to Columns. I was now able to reformat the data to number and then back to date and then everything worked!!!!.
 
Upvote 0
Many thanks Steve 1944. I tried Data/Text to Columns and it worked! Didn't understand how to deal with the "delimiting" questions Excel asked me, but I guessed right. The column had in row one a title (text) and below the one row title the "left justified" troublesome pseudo dated. Choosing a date format during the command made all entries dates. Great. Thanks again.
 
Upvote 0
Many thanks RoryA. The text to column worked. Thanks for the step by step. It all cleaned up (all became dates) nicely.
 
Upvote 0
Hi Steve1944, RoryA's answer fixed the problem for me. Your answer gave me a hint to go to text to columns and I fixed my problem. I don't see the need to go to text->number->date. The dialog box to convert allow to go straight to date format. Thanks for the response. I consider my issue closed.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top