Pivot table help with dates.

[Lythande]

Hey board,

I have a pivot table that looks pretty much like the table on this how-to page:

http://newtech.about.com/od/tutoria...-Pivot-Table-For-Business-Intelligence_10.htm

I want to do exactly what that page is trying to do: convert dates to sort by month instead of by day. The problem is that when I right click on a date header and group,
Excel 2010 (on Windows 7) tells me "Cannot group that selection".

Cell format is already set to date (*14/03/2001) the format that appears at the top
of the list (wonder why the top two have an * by each on the list? Hrm).

Any advice would be greatly appreciated as sorting by month would be most helpful.

 

[Norie]

First try one of the other date formats.

The asterisk * is something to do with regional system settings.

If there is one then the formats will change according to those settings.

Not sure if that would have any impact on the grouping though, that's usually some sort of problem with the source data.

For example blank cells in a date column, dates formatted as text etc.

 

[Marcelo Branco]

Hi,

Did you check your raw data?

Maybe you have "fake" dates...appearing as dates but in fact being texts.

To check try adding 1 to one of the dates in your raw data - if it shows the next day they are real dates; but if they are not you'll see an error like #VALUE...

M.

 

[Lythande]

I went throught the source data and found that the data was in date format, but in 14-Mar-01 rather than the format that the pivot table defaulted to. Just for fun I converted the source data table date format to the sames as the pivot, but alas...it didn't fix the problem.

 

[Norie]

Did you check that the dates were dates, not text formatted as dates?

Did you check for blanks?

 

[Lythande]

When I right click on the date cells and view Format, it shows as Date in the left
hand side then shows the style on the right.

 

[Norie]

That doesn't mean they are 'real' dates.

Goto the raw data an enter this in a spare column:

=ISNUMBER(B2)

Replace B2 with a reference to the first cell in the column with the 'dates'.

 

[Lythande]

Hey Norie,

I did that and it displayed as TRUE. What's my next step?

 

[Norie]

Any blanks in the range that is being used for the pivot table?

Also is the range bening used correct?

eg it includes all you data and nothing else