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 !!!
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,384
Members
412,590
Latest member
Velly
Top