Data loses its chronological ordering in pivot table + cant group by quarter

[Falc7]

So I have two columns, one for the date (monthly) and data (i'll call it rate) corresponding to that date.
I want to group the data into quarters, and find the average rate for that quarter. I've tried making a pivot table, but what ends up happening is the data loses its chronological order, and when I right click on the pivot table date, it doesn't give me the option to group by quarter.
I've uploaded the file here just incase: http://ubuntuone.com/3WinjDvrwyumHUeZm2c5Cs

 

[MD610]

This is more of a general Excel/PivotTable question that a PowerPivot question, but the simple answer is that Excel doesn't like the formatting on the Date field.

For a quick fix, I would create a new 3rd column in your data based off the original date field using a formula like this:

=DATE(RIGHT(B3,4), MID(B3,4,2), LEFT(B3,2))

Use this newly created field in your pivot and Excel should recognize it as a date and behave the way you expect.

 

[Falc7]

That seems to have worked, thanks!
Does the (B3,4) part refer to the contents of cell B3, which is '29.02.1988, and 4 refers to the 1988 part? Is that how it works?
Then MID(B3,4,2) refers to cell B3, what would the 4,2 part refer to in this case?

Once i used that formula, the dates maintained their chronological order in the pivot table, and I can successfully group the dates by quarters and years. I selected average in the pivot table, but it gives me a #DIV/0 error, is there a way to show the average date during the quarter?

 

[Falc7]

bump

 

[mdrew9]

mid requries additional arguments (Start and how many). Is this using Powerpivot? because power pivot has a divide formula, which fixes that error, otherwise just handle the error accordingly.

 

[MD610]

The option to group dates automatically in the pivot is only available to non-PowerPivot PivotTables so this particular forum isn't really the right place for these questions.

RIGHT(), LEFT(), and MID() pull characters from a given field. You supply the field reference and the number of characters to pull (in the case of MID(), you also must tell it where to start pulling).

Once you have it grouped to Years and Quarters, you can show the Average Date by dropping the actual date field into Values, then go into Value Field Settings and change the Count to Average. Lastly, in Value Field Settings, change the number formatting to a Date format.

I assume you are seeing #DIV/0 because you have just tried to drop the grouped Quarters field into Values instead of using the original Date field. Since Quarters are showing in the rows of you Pivot, you will then see the average date for that quarter by following my steps.

 

[Falc7]

I've installed powerpivot now.
Here is where I am at: http://i.imgur.com/uJ7T4ih.png

I'm actually trying to find the average of the 'official discount rate' for each quarter/year. So if the value of the official discount rate is 8 in January, 10 in February, and 12 in March, the pivot table should display 10 as being the average for the first quarter.

If I change the pivot table from 'count' to average, it still gives me the divide by zero error. And i've noticed that if I choose 'sum' the pivot table reports 0 for all quarters and years. I have dragged the official rate field into the values field as you can see from the screenshot above. I haven't dropped grouped quarters into the values field, only the discount rate is in the values field.

If appropriate I can make a new topic in the non-powerpivot table sub forum.