Excel - pivot table does not group dates

Hi guys,


I have been struggling to group the dates in a pivot table to get the month and year hierarchy in addition the days. I would really appreciate if you could help me. I tried almost everything...
Here is how I get my data. 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 a 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 formauls: Iferror...
Can anyone help me with this? I have already used so much time searching and still nothing helps.
Thanks in Advance,
Nanka

A formula like =IFERROR(COLUMN B,"01/01/2018") returns a TEXT string which cannot be grouped ( it is left aligned in a cell if no alignment formatting is applied)

Perhaps try =IFERROR(B1,"01/01/2018"+0) and format as date.


If this does not help, post a small sample sheet. Be aware that if you have empty cells in your date range, grouping will not work either.

PS might I suggest in the future to shorten your explanation a bit and cut it into paragraphs to make it more readable?

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-...t-table-does-not-group-dates.html#post4635196