2

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:

  1. 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.
  2. 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.
  3. 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".
  4. 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

  • Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel. – Scott Craner Sep 19 '16 at 0:49
  • Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab – Nane Amiryan Sep 19 '16 at 2:34
0

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.

enter image description here

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.

  • So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following: – Nane Amiryan Sep 19 '16 at 2:22
  • 1. Dates in the format (9/20/2016) – Nane Amiryan Sep 19 '16 at 2:23
  • 2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018" – Nane Amiryan Sep 19 '16 at 2:25
  • Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field – Nane Amiryan Sep 19 '16 at 2:26
  • Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas – Nane Amiryan Sep 19 '16 at 2:27
0

Your PivotTable is interpreting "01/01/2018" as text rather than as a date.

Instead of
IFERROR(COLUMN B,"01/01/2018"), use
IFERROR(COLUMN B,DATE(2018,1,1)).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.