Excel - pivot table does not group dates

[nanka]

Hi Everyone,

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

 

[Sunny Kow]

Give this formula a try. Assumption is that your date column you are currently using is in Column C and the below formula is copied into column D. It will convert the column C values into date. Now try Pivoting using column D as your date

=IFERROR(DATE(RIGHT(C1,4),LEFT(C1,FIND("/",C1)-1),MID(C1,FIND("/",C1)+1,FIND("/",C1,4)-FIND("/",C1)-1)),C1)

 

[RoryA]

Cross posted here: Excel - pivot table does not group dates

Please read the forum rules on cross posting and follow them in future.

 

[nanka]

Give this formula a try. Assumption is that your date column you are currently using is in Column C and the below formula is copied into column D. It will convert the column C values into date. Now try Pivoting using column D as your date

=IFERROR(DATE(RIGHT(C1,4),LEFT(C1,FIND("/",C1)-1),MID(C1,FIND("/",C1)+1,FIND("/",C1,4)-FIND("/",C1)-1)),C1)

Thank you so much Sunny! It finally worked!
I did two things:
1. Simplified my formulas in the columns. I had different columns and long formulas in each of them that were feeding into my final column which also was constructed by a formula.
2. Second when creating the pivot table I check marked "Add this data to a data Model" and it worked. However I had already applied your code into my formula and then did this 2nd step. It worked out automatically by grouping into months and years.

Then I tried not applying your code and again marked "Add this data to a data Model" when creating the pivot table and the grouping by years and months again automatically worked.

Now I don't know is it marking into a data model when creating a pivot really makes it work - but I am sure your code really helped as well!
Thank you so much again!
I really appreciate your help! This forum is the best! I will come back again if I have any questions and will recommend this forum to anyone. Very professional and fast responses!

Best,
Nanka

 

[nanka]

Thank you! I will!

 

[Sunny Kow]

I am very happy that is worked well for you. Good luck.

Sunny