Pivot table and grouping dates by month

[NJS1982]

Hi, I have a list of dates in a pivot table; I amusing Excel 2016.
I would like to present these dates grouped by month; however I cannot seem to do this?
Apparently when I put dates into a Pivot on Excel 2016 it automatically groups them - it doesn't with these!
Dates are formatted to be DD/MM/YYYY. I can group, when it says 'Group 1', but if I select 'Group selection', no dialogue box appears?

 

[NJS1982]

This is the issue, the popup on selecting 'Group' after the right click doesn't appear, all I see is 'Group 1' within the Pivot table.

http://s000.tinyupload.com/?file_id=91838869227548952388

 

[sandy666]

right click and ungroup first then

look at this: movie

what is your Excel version?

 

[sandy666]

try

Options - Advanced - Data - mark Disable Automatic Grouping of Date/Time columns in Pivot Tables

 

[NJS1982]

right click and ungroup first then

look at this: movie

what is your Excel version?

It seems that even though my source data feeding my VLookup is definitely a date, the VLookup cell that shows the date and then feeds my Pivot is definitely a date, when I create the Pivot and do everything in that video, the alignment on the date column is still on the RIGHT and when I right click and Group, I get that Cannot group that selection popup.

I can't do the text to columns trick on a pivot table, it seems. I get a pop up saying 'We can't make this change for the selected cells because it will affect a PivotTable. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.'

I am using Excel 2016.

 

[NJS1982]

try

Options - Advanced - Data - mark Disable Automatic Grouping of Date/Time columns in Pivot Tables

Done

 

[sandy666]

Dates in the column: Q3 Project Completed are from VLOOKUP formula?
try this: =--VLOOKUP(...etc...
then format this column as short date (dd/mm/yyyy) from the ribbon
and again try to create PivotTable (create new, don't use old PT)

and NO you can't use Text2Columns on PivotTable

 

[NJS1982]

No, still getting the Cannot group that selection popup.
The formula is now this:
=IFNA(--VLOOKUP(A3,'SMSheet'!$A$1:$CN%1000),12,0),"")
I create a new pivot, change the settings under Design as you say then right click, group and that annoying popup appears!

 

[sandy666]

should be: =--IFNA(VLOOKUP(A3,'SMSheet'!$A$1:$CN%1000),12,0),"")

are you sure this is ok? (red marked)

after that try in any cell =ISNUMBER(date_cell_in_your_source_data)

 

[sandy666]

or, if there is no any sensitive information, post a link to shared excel file as is... with whole structure and data type

 

[sandy666]

I am using Excel 2016.

hope this is not 2016 for subcribers aka 365 ?