Forum Discussion
Pivot Table - Date - Group by Month does not work
Dear all,
I am working on my departments budget, and wish to show the costs per month. However, invoices are registered across the month (on 02/01/2019, 05/01/2019, 13/01/2019, ...). Now, the aim is to show per month the costs and not per date it is registered.
- I triple checked that the date field is indeed a date (I can change the date to any date format I'd like)
- I even can use a 'date' timeline
However, when I click on group like here: https://support.office.com/en-us/article/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725
either
- When I select one date, I see "cannot group"
- when I select all dates, it just group the dates
* I restarted Excel
* I verified that the group is date
* I updated to the latest version of excel
10 Replies
Detlef_Lewin I was trying to apply your solution, when suddenly the pivot table itself divided my date into months:
The problem is, I have no idea how I did this. the original table only has 'Date' (not months). He added the field 'month' himself. It is perfect, because this is exactly what I need. (with this, I don't need to group). However, how did I do this? how do I replicate this?
- If you right click on the months in the column of your pivot table is Ungroup and option?
So you have your pivot set like this and then right click on date and you don't get the Group Option?
SBiebaut86 I was having the exact same issue and suddenly it started working after I changed my date format to M, so I guess that's the work around.
