Dates do not group properly

Question

Dear community,

I have a large spreadsheet which I have summarized into a PivotTable.

In columns I have 1. Date 2. Identifier.

The date refers to spreadsheet data which has been brought to me in General format (YYYY-MM). The PivotTable understands the seperate entries but whenever I try to group as date it does not work.

So I reformatted the cells and reformatted the date. The dates now look like 1-1-2000 (does not matter because I can reformat them again in my PivotTable which I use to produce results), they are also recognized as a number (right aligned). However it still does not group. There are no blank cells. Any non date data, I have some are filtered out of the results and it only uses good data.

Why does the group function not work???

Answer 1

Hi,

Try this on a copy of your data first

1. Select the entire dataset (first row should be the header row) and sort on the Date column in ascending order
2. With the entire dataset (first row should be the header row) selected, press Ctrl+Shift+L and apply a custom filter (Equals) on the Date column with * as the criteria
3. Select the resultant non date entries and go to Data > Text to columns > Delimited > Next > Next > Date > MDY > Finish
4. Refresh the Pivot Table twice
5. Select the first date in the date column of the Pivot Table and now try to Group

Hope this helps.

Answer 2

Whenever I filter it says YYYY-MM, ranging from 1994-11 to 2012-07 (original data). These are placed into my column. Whenever I try to group this it gives the "Cannout group that selection".

However, I changed the YYYY-MM to DD-MM-YYYY (with convert Text to Column) and made the whole column format Date (Long Date). This gave me (surprisingly) both the YYYY-MM and DD-MM-YYYY in the filter. The first one, YYYY-MM not containing any data. From there I only filtered the ones containing data out (given on one or two random checks from both the formats, it is an enormous amount of data). This happened to be the DD-MM-YYYY one. Here comes the answer to your question, yes it contains "ungrouped" dates (shown as reformatted to for example 01-11-2001).

Knowing I do not have any blank cells, and the dates are indeed recognized as dates (did the check where it returns the serial number on the original spreadsheet) it still gives me the same damn error.

I now grouped all my data by hand, which surprisingly, the PivotTable does accept. But I sure would like it if Excel would accept my group request. My dataset contains about 60k entries, and I have to do at least 10 more this way. I am sure I am doing something wrong but I can not seem to get the grasp on it.

Thanks for your help in advance!

Answer 3

Hi,

When you select the entire dataset of your base data and filter on the date column, do you see all dates getting grouped into months and years OR are there any ungrouped dates appearing as well?

Answer 4

The date refers to spreadsheet data which has been brought to me in General format (YYYY-MM).

Hi,

if you have the dates in Source Table, like... 2000 JAN or 2013 FEB or 2013-02

(whatever format try to use)

i mean that, if in Genaral formatting you can see 2013 FEB or 2013-02 then,

this is NOT a date but a text string,

so, you might change/retype, on Source Table the dates as dates,

in order to can handle, by grouping (in Pivot Tb).

Answer 5

Copy your data (don't copy whole sheet, just rows which are having data, without taking even a single blank row) to new Test workbook, there change the cell formatting of date cells to LONG DATE, just to ensuare once again that everything is in date format.  In data column give some number like 1 to 100. Now again insert a pivot table and see whether Group function for date is working or not.

I hope you will find some reason why its not workiing in your original place.