Pivot table - report filter date formatting

[kenny9002]

I have a Data Table containing details of Sales and Payments Received, and from which I intend to create a Pivot Table for regular analysis.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
The Data Table contains about 7 columns, two of which are date columns as follows:
a) Invoice Date
b) Date Paid.
<o> </o>
The dates currently run from Jan 2007 to April 2011.
<o> </o>
The dates are formatted as follows:
a) Invoice Date (mmm-yy) using the formula [ =Date(Year(x),Month(x),1) ]
b) Date Paid (mmm-yy) using the formula [ =Text(x,”mmm-yy”) ]
<o> </o>
Problem:
My problem is with the Date Paid column which I drag into the Report Filter.
When I create a Pivot Table, the Report Filter drop down menu (which houses Date Paid lists the dates paid (which are in months/years) in alphabetical order and not in calendar/year order. Infact, I get something in the following order:
<o> </o>
Apr-07<o></o>
Apr-08<o></o>
Apr-09<o></o>
Apr-10<o></o>
Apr-11<o></o>
Aug-07
Aug-08
Aug-09
Aug-10
Dec-07
Dec-08
Etc
<o> </o>
How can I format my Pivot Table so that the Report Filter Drop Down Menu arranges the Dates Paid (which are shown in mmm-yy format) by Year and Calendar order.<o></o>
<o> </o>
Meanwhile, I need to explain that the reason I formatted the said ‘Date Paid’ column using =Text(x,”mmm-yy”) is because the column contains the text ‘UNPAID’ for those invoices yet unpaid. In my analysis, I would want to know what invoices were paid in which month/year and also the invoices that are yet Unpaid. When I click ‘Unpaid’ from the Report Filter drop down menu, it comes up with the information I want. My problem is to get the Report Filter Drop Down menu to arrange the months/years in calendar order.
<o> </o>
Thanks for all your anticipated help.

Kenny

 

[Datsmart]

You are taking Excel "Dates" and changing them into Text, then you want to Sort them.
If they were still dates you could, but since you changed them to Text you can not.

I would re-think using those date to Text formulas.

 

[kenny9002]

You are taking Excel "Dates" and changing them into Text, then you want to Sort them.
If they were still dates you could, but since you changed them to Text you can not.

I would re-think using those date to Text formulas.

Hi John,

May I know if you have a suggestion on how I can achieve what I want to do. I need to be able to choose, from the Report Filter Drop Down Menu, the month/year paid in calendar format and also be able to choose 'unpaid' invoices.

As I indicated in my post #1, I have a column that houses the dates each invoice was paid, including the word 'unpaid' typed against each of the unpaid invoices. When I tried to format that column using the date format, those cells with the word 'Unpaid' showed 'Error', but when I formatted the column as indicated in my post #1, I had no error showing in any of the cells in the column. Is there any other approach to this that will enable me to achieve what want to do.

Could any of the forum Excel experts help, please.

Thanks all.

Kenny