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]

Your dates are text not a number that is why you cannot group by month

OK, so I formatted the source data as a date (DD/MM/YYYY) also the pivot column; still says I cannot group the data?

 

[sandy666]

did you see example file?

 

[NJS1982]

I have, I formatted them as DD/MM/YYYY (Format Cells, then select Date and chosen DD/MM/YYYY (on my type list it shows it as 14/03/2012).
When I now select group, it creates a group called 'Group 1'; but how do I then make it group by months?

 

[NJS1982]

This is EXACTLY what I am doing:

1) I have a sheet, column A is the URN and column B is the Date, formatted as DD/MM/YYYY
2) I select all of the data, choose the Insert tab and select Pivot Table
3) The prompt then creates the pivot on another tab
4) I add URN to the Values area, changing it to COUNT rather than SUM
5) I add Date to the Rows area
6) I then remove the blanks using the filter on the rows column
7) I select one of the dates, go to the 'Analyze' tab and select Group Selection. A pop up appears saying 'Cannot group that selection'
8) I then select all of the table, do the same, I get 'Cannot group that selection'
9) I then highlight just the dates, do the same, and it then adds a '-Group 1' underneath the Row Labels header
10) I then have no idea what I can do, I am stuck.

I have read somewhere that Excel 2016 should automatically create groups; I have seen it do it in the past but it isn't for this set of data?
Have I perhaps changed some of the options or display settings along the way?

 

[sandy666]

but YOUR dates aren't Excel dates but text whatever you'll choose from the ribbon it still will be a text! In Excel Date is a NUMBER!

try change format to General, if you still see dates , your dates are text (which is incorrect) , if you see a numbers - your dates are real dates

select dates in date column in your source table, go to Data - Text to Columns, Delimited, Next, Next, select Date: DMY, Finish
and then create PivotTable

did you see picture in previous post????

 

[sandy666]

don't use formatting from the ribbon until the result will be correct

 

[NJS1982]

OK, I did the Data 'Text to Columns' steps, then as a test I formatted the column to be general - the date still displays, so it is incorrect as you say. Format the sheet, why can't I do this? I don't understand, sorry! It all sits on a sheet that has been formatted all over.

 

[sandy666]

text is aligned to the left by default
number is aligned to the right by default as you can see on the last picture

if you use the alignment option then you will not know what type of data you really have.
you can do that on the end if all your work is working

formatting from the ribbon works on the appearance of data and not the real format

 

[NJS1982]

OK. SO the numbers are now definitely date formatted; checked and when set to General they appear as a number.
I have pivoted the data and still getting the same issue.
How do I group the date data in the Pivot?
Cannot group that selection is all that appears!

 

[sandy666]

delete old pivot table
create new
drag Q3 Project Completed into the Rows Area
drag Q2 URN into the Values Area (set it to count)

in Pivot Table on dates column right click, Group , select Months (doesn't matter it is highlighted, you need to click this)

then replace Q3 Project Completed with Q3 Project Completed (Month)

all what you need you've in my example file from post #10