Group Dates in Pivot Table

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
I have a simple Pivot table just Dates plus Data.

I then right click the Date heading and tried to group dates.

However, it said "Cannot Group that Selection".
I have already removed all blank cell in Date field.

What can be wrong ??

Help !!!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Sometime I hate pivot tables!

Anyway - this is a little odd, but it worked for me - you need to refresh the pivot twice before the grouping kicks in again?!?

What I did:

1) Set up list with dates & gap in list
2) Set up pivot table
3) Tried to group on dates - got the error message
4) Filled in the missing date
5) Tried to group on dates - got the error message
6) Refreshed the data
7) Tried to group on dates - got the error message
8) Refreshed the data again
9) Tried to group on dates - grouping box pops up.

Tested a few times & seems to work...
 

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
Thanks..

Just tried with no luck.

Anyone can tell what exactly is the prerequisite to do this Date Groupings (eg into weeks, month etc).

I used very simple dummy data and still did not work. ****...
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Minimum is having genuine date values in a contiguous range. What are your date values?
 

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679

ADVERTISEMENT

Just this

Date Area
01-Apr-05 66
01-Jun-05 66
02-Feb-05 66
02-Nov-05 66
03-Nov-05 66

Help...
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
what does:

=isnumber(ref)

...return, where ref is one of your date cells?
 

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679

ADVERTISEMENT

Oh you find the root of the problem. It is False. How to solve this problem ?

Thanks !!!
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
highlight the column of 'dates', go to data | text to columns | hit next twice | select date & DMY from the drop down | hit finished
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,907
Members
431,772
Latest member
dannyboi1

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
Top