Date sorting in Excel 2007

[celli57]

Hope someone can help - I am trying to create a pivot table of data which
covers 9 years and the first thing I want to do is sort the date into months
and quarters. When I take the date across to the table and try to group them
it is telling me I cannot group the selection and I notice the dates are
listed as
1/1/2000
1/1/2001
1/1/2002
1/1/2003
1/1/2004
1/1/2005
etc then the next day follows again sorted by each year. Any suggestions
would be welcome

Thanks

 

[Shane Devenshire]

Hi,

It looks as though your dates are not Excel dates but text enties. You must
convert them to date. The following might work:

Select an empty cell and choose Copy
Select all the dates in the data source, not the pivot table and choose
Home, Paste, Paste Special, Add.

If this works you will want to format the cells as dates.

 

[Dave Peterson]

You can't group non-numeric fields. And it sure looks like your dates aren't
really dates.

If you put:
=counta(a2:A999)
and
=count(a2:a999)
in a couple of helper cells in the raw data sheet, do you get the same answer?
(adjust the range to match the range of "dates".)

My bet is that some of your dates are just plain old text. You can see this if
you change the numberformat to a non-ambiguous date format: mmmm dd, yyyy

One way to change those non-date dates to real dates is to select the range
(single column at a time) and use:

Data|text to columns (xl2003 menus)
Choose fixed width
Remove any lines that excel guessed
choose the right order for the dates (ymd, dmy, mdy, ...)
(matching the order of the text dates)
and finish up the wizard/dialog.

Now format your range the way you like.

And refresh your pivottable to see if you can group the field.

If you can't do the grouping, maybe you have empty cells in that range (that's
bad, too).

If you're sure everything is ok, try rebuilding the pivottable and then try
grouping.