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]

Try using IFERROR(COLUMN B,date(2018,1,1))

 

[nanka]

Try using IFERROR(COLUMN B,date(2018,1,1))

Thank you Sunny Kow. I tried, still just showing Group 1 instead of month-year hierarchy. i tried changing these dates inside pivot into general and few of them remained as dates, the rest became numbers. Does it mean these few that stayed as dates are fake dates?

 

[Sunny Kow]

If any cell in the column is not a date, you will not be able to group. Widen the column with the dates. If any value is left-align then it is likely not a true date.
Dates and numbers are right align (unless you manually align them)

 

[nanka]

If any cell in the column is not a date, you will not be able to group. Widen the column with the dates. If any value is left-align then it is likely not a true date.
Dates and numbers are right align (unless you manually align them)

I tried righ-aligining them in the original table and then in the pivot. Here is what i get below.
1. Is it possible to change the group 1 into months? Generally I should be able to get the window with either months or years which i am not getting.
2. Is it because in the original table the dates are formulas - that's why? I am not copying and pasting because i am refreshing this file so need the dynamic new data from SQL.

Group1

01/01/2018

03/13/2016

10/02/2016

10/09/2016

10/16/2016

10/23/2016

10/30/2016

11/06/2016

11/13/2016

11/27/2016

12/18/2016

9/20/2015

9/27/2015

11/15/2015

11/22/2015

12/6/2015

12/20/2015

1/10/2016

1/17/2016

1/31/2016

2/14/2016

3/6/2016

3/13/2016

4/24/2016

5/1/2016

5/22/2016

5/29/2016

6/5/2016

6/12/2016

6/19/2016

6/26/2016

7/3/2016

7/17/2016

7/24/2016

7/31/2016

8/7/2016

8/14/2016

8/21/2016

8/28/2016

9/4/2016

9/11/2016

9/18/2016

9/25/2016

10/2/2016

10/9/2016

10/16/2016

10/23/2016

10/30/2016

11/6/2016

11/13/2016

11/20/2016

11/27/2016

12/4/2016

12/11/2016

12/18/2016

12/25/2016

1/8/2017

1/15/2017

1/22/2017

2/12/2017

12/31/2017

<colgroup><col></colgroup><tbody>
</tbody>

 

[Sunny Kow]

Hi nanka
Do not manually align the dates in your column. Let them be general. Any value that is left-align is not recognized as a date. You will then need to check on these values.
Manually aligning them does not change them to a date.

 

[Sunny Kow]

One more question. Are you using US (mm/dd/yyyy) or UK (dd/mm/yyyy) date format?

 

[nanka]

Thank you Sunny. How do you suggest to proceed here? The dates are coming from SQL, then I am putting them into formulas to convert nulls or blanks with the iferror function. How do i solve this?

 

[nanka]

Thank you. I am using US (mm/dd/yyyy) format.

 

[Sunny Kow]

You will first need to see if Excel is recognizing these as dates. Follow post #6 and let me know the result.
What formulas are you using in column B and C to extract the "date" of column A?