Problem grouping dates in Pivot table 2

[Mahathma]

I am trying to group dates to months in Pivot table.
It worked once. But, subsequently I keep getting error message 'Cannot group that selection'.
I read other related postings in this site and did exactly as given. But, still does not work.
Please help.
Regards



Guru

 

[Mahathma]

I fiddled around and discovered something.
The error was due to blank lines. This criteria is not mentioned in any help file.


Guru

 

[KenWright]

Either blanks or Text values in your Date field will cause this to happen. Spend a few minutes doing some data cleansing initially before you create your tabel and it will pay off in spades.

The easiest way to combat what you just came up against depends on the cause of your blanks. If they are complete blank lines then get rid of them. Pick any column that you can say for sure that if the cell in that column is blank then the row can be deleted. Select that column, do edit / Go To / Special / Blanks and then do Edit / Delete / Entire Row.

If your data simply has missing date fields for whatever reason, and putting in a date in the blanks would not skew your data in any way then choose any date within your range (I usually choose the earliest), and put that into all the blanks. This can be done simply by selecting your entire data range, do edit / Go To / Special / Blanks and then type the date and use CTRL+ENTER to enter it. Thsi will populate every blank cell in your date range with that date.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------