Group Dates in Pivot Table

[mrchonginhk]

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

 

[PaddyD]

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]

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]

Minimum is having genuine date values in a contiguous range. What are your date values?

 

[mrchonginhk]

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]

what does:

=isnumber(ref)

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

 

[mrchonginhk]

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

Thanks !!!

 

[PaddyD]

highlight the column of 'dates', go to data | text to columns | hit next twice | select date & DMY from the drop down | hit finished

 

[mrchonginhk]

Wow it works !!! Thanks !!!

U are the best !!!

 

[PaddyD]

glad you've got it sorted