Grouping Dates in Pivot Table

[KKIndle]

Sometimes I can group on dates in Pivot Tables and
sometimes not. I have checked to confirm that the dates
are numeric fields instead of text fields thinking that
perhaps that was the problem, but they are numeric.

Can anyone suggest anything I can do to diagnose the
problem? (I know how to do the grouping ... but am not
getting the appropriate dialog boxes from the group and
outline dialogs.)

Thanks in advance, KKindle

 

[Debra Dalgleish]

If there are blank cells in the date field, you won't be able to group
the date. Perhaps that's the problem.

 

[KKindle]

No, there are no blanks, but good thought.

-----Original Message-----
If there are blank cells in the date field, you won't be able to group
the date. Perhaps that's the problem.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

 

[Ken Wright]

Because one or more of the records in that field is either BLANK or is TEXT.
When grouping dates, if you have for example 1000 lines of data, you MUST have
1000 dates, and they must be REAL dates and recognised as such by Excel.

A simple test assuming your dates are in Col A such as in any other column;-

=A2+1

and copied down will tell you if the data is a valid date or not.

=ISTEXT(A2) will tell you if any of the data is textual, but the first example
should do it. Just look for any errors such as #N/A etc

 

[Debra Dalgleish]

You could base the pivot table on a dynamic data source, and it will
exclude the blank rows at the end of the worksheet. There are
instructions here:

http://www.contextures.com/xlPivot01.html