Pivot table grouping into months/ years not working

JitkaO

New Member
Joined
Oct 3, 2019
Messages
16
hello
please can someone help me. I am using Excel 2016 and in my pivot table, I tried to group my date rows (example 11/12/2018) into months or years.
However when I click on 'Group Selection', it automatically assigns Group 1, rather than giving me options to sort into years and months.
I can only group into Group 1 when I highlight all the date data in my pivot, when I click on just 1 cell it tells me 'it cannot group that selection

I just really need to be able to sort into years and then months.

Not sure if this was part of the issue but I don't seem to be able to amend the date format in the fields to any different date format.

Any help much appreciated.
Thank you
Jitka
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Options - Advanced - Data - uncheck Disable automatic grouping .. etc.

Grouping works with real dates not with text looks like dates
 
Upvote 0
Sounds like your dates arent really dates but text. You can find out by using =ISNUMBER(A1) but pointing it to a date.
 
Upvote 0
Thanks for getting back to me so quickly. I double checked and the date format in my raw data is set as 'date'.
When I click on the pivot table, the advanced option is greyed out.

<tbody>
</tbody>
 
Upvote 0
Formatting wont change a text date to a date. Test it with ISNUMBER as suggested.
 
Upvote 0
as test I tried to change the formatting of the date to be in a different string and that is not working. I wonder if there is an issue with the actual spreadsheet, it's saved as worksheet
 
Upvote 0
Thanks, sorry only just saw the above formula, it returns false.
Is there a way I can change this on my raw data with some clever formula, what is the way round this please?
 
Upvote 0
Try clicking the column to highlight it then press data-text to columns and then finish. They should revert to dates and the isnumber test is now true.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top