Cannot group selection

[Damian37]

Hello all,
I'm working on a spreadsheet where I have numerous pivots. Some pivots I'm able to group the date field, however there's one pivot that I'm unable to group the same date field I'm able to in other pivots. Does anyone know why this might be happening? I've tried creating a new pivot, but continue to receive the same "cannot group selection" error. Any help would be greatly appreciated. Also, I am not selecting the data model option when creating my pivot. I am also using this formula in order to convert the original date which contains a time stamp and time zone to a flat date.
<code>
=IF(ISBLANK(G2),"",TEXT(IF(CELL("type",G2)=CHAR(108),LEFT(G2,FIND(" ", G2)),INT(G2)),"mm/dd/yyyy"))
</code>

Thank you.

D.

 

[sandy666]

try to make source od these dates as real date not as text

 

[Damian37]

try to make source od these dates as real date not as text

Should I highlight the column and change the format to date? I've tried to manually change the format of the dates, but it still doesn't allow me to group the selection. I'm not sure why.

 

[sandy666]

I don't know what you have there but you can try:
=IFERROR(--IF(ISBLANK(G2),"",TEXT(IF(CELL("type",G2)=CHAR(108),LEFT(G2,FIND(" ", G2)),INT(G2)),"mm/dd/yyyy")),"")
then probably you'll need to set date format for this column

what kind of data is in G2?

 

[Damian37]

I don't know what you have there but you can try:
=IFERROR(--IF(ISBLANK(G2),"",TEXT(IF(CELL("type",G2)=CHAR(108),LEFT(G2,FIND(" ", G2)),INT(G2)),"mm/dd/yyyy")),"")
then probably you'll need to set date format for this column

what kind of data is in G2?

It is a date, however, in its original format the date also contains a time stamp and time zone. The formula removes the time stamp and time zone without performing text to columns prior to creating pivot.
D.

 

[sandy666]

I tested on this

12/06/2019 00:00

06/12/2019​

and it works

format for blue cell is short date (from the ribbon)

edit:
my date format is: dd/mm/yyyy that is why you see difference

did you try group your PivotTable now?

 

[Damian37]

I don't know what you have there but you can try:
=IFERROR(--IF(ISBLANK(G2),"",TEXT(IF(CELL("type",G2)=CHAR(108),LEFT(G2,FIND(" ", G2)),INT(G2)),"mm/dd/yyyy")),"")
then probably you'll need to set date format for this column

what kind of data is in G2?

Thank you so much for this formula update. This worked out, and I am now able to group the dates. The data in G2 is Created On dates, however the original format the date comes in is with a timestamp and time zone which need to be removed prior to refreshing existing pivots. Thank you so much for your help!

D.

 

[sandy666]

I am glad it works for you

Have a nice day