Pivot table grouping into months/ years not working

[JitkaO]

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]

Options - Advanced - Data - uncheck Disable automatic grouping .. etc.

Grouping works with real dates not with text looks like dates

 

[steve the fish]

Sounds like your dates arent really dates but text. You can find out by using =ISNUMBER(A1) but pointing it to a date.

 

[JitkaO]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Formatting wont change a text date to a date. Test it with ISNUMBER as suggested.

 

[JitkaO]

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]

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?

 

[steve the fish]

Are they formulas or just typed dates?

 

[JitkaO]

They are exported from a cloud but essentially typed, not formulas. Thank you

 

[steve the fish]

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.