Pivot Table

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have set up a pivot table with dates in Col A. I would like to group the dates into months. however, when I select Col A and select group. I get a message "Cannot group that selection"

I have posted some sample data

Your assistance in this regard is most appreciated



Excel Workbook
AB
3DatesSales
413/07/2012 15:35751.79
513/08/2012 12:33391.1
613/08/2012 15:19172.5
713/08/2012 15:2973.5
814/07/2012 09:002433.3
914/08/2012 12:02879.05
1014/08/2012 14:021866.18
1114/08/2012 16:228451.7
1214/08/2012 16:31840.75
1314/08/2012 16:4170
1415/08/2012 08:112430.79
1515/08/2012 08:34119.84
Sheet4



http://windowssecrets.com/forums/sh...es-in-Months-Pivot-Tables?p=868824#post868824
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
(BTW, I haven't used the link posted.)

Maybe, Howard, not every single record has a time entry?
 
Upvote 0
Hi Fazza

Thanks for the reply. All the records have a time entry-The data is imported. Is there a way to remove this and then group the dates by month?
 
Upvote 0
In my experience, if all entries are dates it should work. Maybe they are being treated as text?

You could check by formatting as dates, CTRL-SHIFT-3. If they stay as they are, then they are text & the problem is identified. If they format as dates, the confusion continues.

If they are text, to get to numbers there are probably a few ways. As you have a data import, maybe you have some SQL and can add a function to force that field to be numeric? Or multiply the values in the current field by 1. Or some other method.

Howzat?
 
Upvote 0
If you don't mind adding in a new formula in column C and then including that in your pivot table definition, you can use the =TEXT(A4,"YYYY-MMM") in Cell C4 and then copy that down. If the value is properly stored as a date time stamp then you will end up with text values in C that correspond to the Year and Month. You can then use these as a grouping criteria in your pivot table.
 
Upvote 0
Hi Fazza

Thanks for the reply. I used CTRL-SHIFT-3 and it is definately text. I have used the formula kindly supplied by symanuk
 
Upvote 0
Thanks for the reply. Ypour formula works perfectly
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top