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?
- 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