I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script.
For some reason when I create a pivot table from this raw table I am getting an error message saying "Cannot group this selection". I checked I don't have any blank data, the column is in date format. I tried everything, tried to convert from test -to -columns to dates, that in turn brings an error. There is something really wrong with it – not sure what.
Can someone help me, please?
More clarification:
- My Raw Data is a table that is refreshable thru embedded SQL script that has a column with dates – let's say it's column A.
- This column A that is populated from SQL has dates in the format 01/20/2016 and also has blanks. I am doing few steps thru formulas to create another column – column B. As i need to return the Sunday of that specific date – for example if the date is saying 9/20/2016 – column B will return 09/18/2016.
Bascially Column B has the dates equal to the dates in column A, and whatever is blank in column A is #N/A in column B. - I am creating column C to grab the dates from column B in the following way: Column C=IFERROR(COLUMN B,"01/01/2018"). So whatever in #N/A in column B I am saying let it be equal to "01/01/2018".
- I am creating a pivot table from this raw data table and dragging the column C as it's mu date column. Then I want to group these dates into months and years as well. However I am either getting the error "Cannot group this selection" or if it is grouping it is creating another column called Group 1 which is not what i am looking for, don't know if it can be converted to months or years?
on the home tab there is a [Group Selection] section which is greyed out. I think if it is not greyed out it will be able to give me the option of choosing month and year hierarchy.
I checked column c ( my date column) there are no blanks – the format it 1/2/2016, don't know why it's not grouping.
I tried [Text to Columns] within pivot table, got an error. I tried [Text to Columns] in the raw data table it is converting columnn C into it its actual formulas: Iferror
Best Answer
There may be a locale issue. When dates are stored as text, converted to dates will work fine if the date is 1/1/2016, but will not properly convert all dates if your locale uses DMY and the text date is using MDY (or the other way around). Test a cell in your data source that has a day value greater than 12 and ensure that it is returned as a proper date.
After checking, turn your data source into an Excel Table Object with Ctrl-T or Insert > Table. Then use the filter drop-down on the date column. If the column contains only dates, you should not see individual entries, but a year/month/day expandable tree.
Also, double check for blanks. Maybe your pivot data source includes a blank row at the end of the source data.
Edit: If you get an error when doing text-to-columns to dates, then that is a clear indicator that the text cannot be interpreted as a date. In the Text to column wizard, ensure that you set the correct order of day, month and year, as it is in the source. In the screenshot you can see that the date is in the order MDY. My locale uses DMY by default, so I need to let the text to column wizard know that the source data is in MDY order.
Edit2: From your comments it looks like you are using a formula to construct the date. A formula cannot be processed by text to columns (why on earth would you do that????).
Ensure that the formula you use to create the dates returns real dates, not text that looks like a date. Edit your question, provide the details of the formula for inspection. And -- please, hold off with the comments. This is not a chatty forum.