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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,240
Options - Advanced - Data - uncheck Disable automatic grouping .. etc.

Grouping works with real dates not with text looks like dates
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,823
Office Version
365
Platform
Windows
Sounds like your dates arent really dates but text. You can find out by using =ISNUMBER(A1) but pointing it to a date.
 

JitkaO

New Member
Joined
Oct 3, 2019
Messages
16
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>
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,823
Office Version
365
Platform
Windows
Formatting wont change a text date to a date. Test it with ISNUMBER as suggested.
 

JitkaO

New Member
Joined
Oct 3, 2019
Messages
16
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
 

JitkaO

New Member
Joined
Oct 3, 2019
Messages
16
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?
 

JitkaO

New Member
Joined
Oct 3, 2019
Messages
16
They are exported from a cloud but essentially typed, not formulas. Thank you
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,823
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,085,490
Messages
5,383,963
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top