Excel - pivot table does not group dates

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

Nane Amiryan

Posted 2016-09-19T00:43:18.147

Reputation: 29

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 – 2016-09-19T00:49:58.053

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 – 2016-09-19T02:34:54.200

Answers

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.

teylyn

Posted 2016-09-19T00:43:18.147

Reputation: 19 551

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 – 2016-09-19T02:22:59.180

Dates in the format (9/20/2016)

< – Nane Amiryan – 2016-09-19T02:23:32.837

start="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 – 2016-09-19T02:25:34.767

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 – 2016-09-19T02:26:20.690

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 – 2016-09-19T02:27:51.927

Do you know how to solve this problem? – Nane Amiryan – 2016-09-19T02:28:06.247

Whoa! This is not a chatty forum. Please stop putting all this into comments. Edit your question and put the details into your question. Then post a comment to alert people that you have made changes. – teylyn – 2016-09-19T03:02:10.023

I edited my answer. – teylyn – 2016-09-19T03:05:18.657

I am very sorry. I am first time to this forum. I edited my answer but not sure if it's seen to the audience. Please, let me know if it's not seen and I will try again. Thanks! – Nane Amiryan – 2016-09-19T03:22:30.080

What answer? You need to edit your QUESTION. – teylyn – 2016-09-19T03:43:24.943

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)).

Dan Henderson

Posted 2016-09-19T00:43:18.147

Reputation: 865