Pivot Table: “Cannot Group That Selection” error

[steveski]

I have a data entry sheet with a column titled “Start Date”. This column contains only dates.

I have a Pivot Table based on the data entry sheet. In the Pivot Table “Start Date” is a Column Field. Start Date is also used as the data field (Count of Start Date).

I clicked on the “Start Date” In the Column Filed area and selected Grouping by Month & Year.

While I was developing the spreadsheet, I used some arbitrary dates in the Start Date column, and the grouping worked fine. Then I brought in a column of dates from the ‘real’ data entry sheet, and now the grouping is not functional (“Cannot Group That Selection” error).

Can you tell me some possible causes of this error?

Thanks.

 

[josevi]

Make sure that

a) there are no empty spaces in the data
b) all the dates are real dates (You can check that by changing the format in the database, and if there are some dates that don't change, they need to be "converted" to numbers again)
c) try creating the pivot table again.

Hi, JPG, I tried "changing the format" using cell formatting (e.g. "short date"), but no change took place.
A curious thing: there are dates that are right justified (they are the real dates; e.g. 1/14/2013) and the exact same looking date 1/14/2013 only LEFT justified which is NOT the real date. Double clicking on the "non date", then moving the cusor to another cell converts the non left justified date to a right justified real date. Question: How can I convert non dates to dates "en masse"? I have 39k entries! Thanks.

 

[parentheticalclaws]

Hi, JPG, I tried "changing the format" using cell formatting (e.g. "short date"), but no change took place.
A curious thing: there are dates that are right justified (they are the real dates; e.g. 1/14/2013) and the exact same looking date 1/14/2013 only LEFT justified which is NOT the real date. Double clicking on the "non date", then moving the cusor to another cell converts the non left justified date to a right justified real date. Question: How can I convert non dates to dates "en masse"? I have 39k entries! Thanks.

To do this, select the column containing the dates, choose the "data" menu in the ribbon, and click "Text to Columns." Leave the selection as "Delimited" and press "Finish." This should have the same effect as manually double-clicking each cell.

 

[josevi]

Selecting Data/Text to Columns/Delimited worked. Thank you!