Grouping dates in pivot table by quarters

[Nadine]

I created several pivot tables which contain information by month. In order
to reduce the number of months I would like to group them by quarters but
this does not work! I already checked the data base and chose yyyy/mm for
the data but still I get the message 'cannot group for this selection'. There
are no empty cells either. Anyone got an idea please?
I use excel 2002.

 

[Roger Govier]

Hi Nadine

Grouping by Date fails if there are empty cells, or cells containing Text
values.
Although you say that you chose yyyy/mm as the format for the column, that
will not alter the underlying entries in the cells if they were entered as
text as opposed to true Excel dates.

To check, using a spare column on your sheet, enter
=ISNUMBER(A2) (assuming the data is in column A, adjust to suit)
Copy down as far as required.
If the values are Dates, the result will be TRUE. Check to ensure that all
are true.

 

[Steve]

If you had a column added to your data, you could do a vlookup on a table
which had the date range for a quarter, or a table with months 1,2,3 in 1st
quarter,etc.
Steve

 

[Nadine]

Hi Nadine

Grouping by Date fails if there are empty cells, or cells containing Text
values.
Although you say that you chose yyyy/mm as the format for the column, that
will not alter the underlying entries in the cells if they were entered as
text as opposed to true Excel dates.

To check, using a spare column on your sheet, enter
=ISNUMBER(A2) (assuming the data is in column A, adjust to suit)
Copy down as far as required.
If the values are Dates, the result will be TRUE. Check to ensure that all
are true.

--
Regards
Roger Govier



Hello Roger,

You were right indeed! I tried out the formula =number and all the results
were 'not true'. I'll contact our ICT-service in order to try and change the
information into dates. Thank you very much!

 

[Nadine]

If you had a column added to your data, you could do a vlookup on a table
which had the date range for a quarter, or a table with months 1,2,3 in 1st
quarter,etc.
Steve

Hi Steve,
This might indeed be an option but since the data covers different years, I
believe it would take too many entries in the formula...