Grouping dates in Pivot Tables

Jill (Australia) · May 19th, 2002, 09:28 PM

Using data copied from an Access database which has been inputted from many different computers (I suspect with different regional settings) I am having problems grouping dates. Sometimes it works properly, but more often I get an error message which states "Cannot group that selection". I have tried copying the formatting from date colums which do group into those which don't, without success.

Any advice much appreciated.

Thanks

Jill

Yogi Anand · May 19th, 2002, 09:37 PM

Hi Jill:
Welcome to the board. In regard to the dates coming from various different settings not being properly recognized, have you tried to coerce the entries into numbers by adding a 0 to each, or multiplying each affected entry by 1.

Regards!

Jill (Australia) · May 19th, 2002, 11:47 PM

Many thanks, I actually did solve the problem, but will keep this for future reference.

dmschroer · Aug 2nd, 2002, 09:24 AM

I am having this same problem. I tried your suggestion of adding zero or multiplying by 1, but still getting "cannot group that selection." Any other ideas?

Mark W. · Aug 2nd, 2002, 12:13 PM

Quote:

On 2002-08-02 08:24, dmschroer wrote:
I am having this same problem. I tried your suggestion of adding zero or multiplying by 1, but still getting "cannot group that selection." Any other ideas?

Is (blank) listed as one of your dates?

dmschroer · Aug 2nd, 2002, 02:03 PM

Yes, I noticed that sometimes if one of the cells in the field for the pivot table is blank (no date entered), that was a problem (by the way, is there a fix for that?). In this case, I made certain every cell in that field that I am trying to group has a date entered - yet, I still can't group the dates.

[ This Message was edited by: dmschroer on 2002-08-02 13:06 ]

Mark W. · Aug 2nd, 2002, 02:11 PM

Since PivotTables "remember" all item values you'll have to re-create the PivotTable. You cannot Group dates unless all items are indeed date values.

dmschroer · Aug 8th, 2002, 09:40 AM

Thanks to all for the advice. Your suggestions were great and extremely helpful! My problem solved!

Since I was grouping by weeks, I also discovered you can use =WEEKNUM() formula and bring that into the pivot table.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 19th, 2002, 09:28 PM	#1
Jill (Australia) New Member Join Date: May 2002 Posts: 3	Using data copied from an Access database which has been inputted from many different computers (I suspect with different regional settings) I am having problems grouping dates. Sometimes it works properly, but more often I get an error message which states "Cannot group that selection". I have tried copying the formatting from date colums which do group into those which don't, without success. Any advice much appreciated. Thanks Jill

May 19th, 2002, 09:37 PM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Jill: Welcome to the board. In regard to the dates coming from various different settings not being properly recognized, have you tried to coerce the entries into numbers by adding a 0 to each, or multiplying each affected entry by 1. Regards!

May 19th, 2002, 11:47 PM	#3
Jill (Australia) New Member Join Date: May 2002 Posts: 3	Many thanks, I actually did solve the problem, but will keep this for future reference.

Aug 2nd, 2002, 09:24 AM	#4
dmschroer New Member Join Date: Aug 2002 Posts: 9	I am having this same problem. I tried your suggestion of adding zero or multiplying by 1, but still getting "cannot group that selection." Any other ideas?

Aug 2nd, 2002, 02:03 PM	#6
dmschroer New Member Join Date: Aug 2002 Posts: 9	Yes, I noticed that sometimes if one of the cells in the field for the pivot table is blank (no date entered), that was a problem (by the way, is there a fix for that?). In this case, I made certain every cell in that field that I am trying to group has a date entered - yet, I still can't group the dates. [ This Message was edited by: dmschroer on 2002-08-02 13:06 ]

Aug 2nd, 2002, 02:11 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Since PivotTables "remember" all item values you'll have to re-create the PivotTable. You cannot Group dates unless all items are indeed date values.

Aug 8th, 2002, 09:40 AM	#8
dmschroer New Member Join Date: Aug 2002 Posts: 9	Thanks to all for the advice. Your suggestions were great and extremely helpful! My problem solved! Since I was grouping by weeks, I also discovered you can use =WEEKNUM() formula and bring that into the pivot table.