I have a Data Table containing details of Sales and Payments Received, and from which I intend to create a Pivot Table for
The Data Table contains about 7 columns, two of which are date columns as follows:
a) Invoice Date
b) Date Paid.
The dates currently run from Jan 2007 to April 2011.
The dates are formatted as
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”) ]
In order to format the 'Date Paid' column, I opted
for the =TEXT(G2,("MMM-YY") format over the =DATE(...) format, because the =DATE(...) format was returning error for all the
'Unpaid' values included in the column.
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’ arranges 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:
How can I format the Pivot Table so that the Report Filter Drop Down Menu will show the 'Dates Paid' (which
are formatted as mmm-yy) by Year and Calendar order, instead of in the alphabetical order as shown above.
to explain that In the 'Date Paid' column in my Data Table, I type the word 'Unpaid' against those invoices that are
currently unpaid, because I would want to be able to filter and report on both the ‘paid’ and ‘unpaid’ invoices using the
Pivot Table, when the information is required.
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. It
also contains the ‘dates paid’. In my analysis, I would regularly 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.
Someone has indicated to me that the reason I'm unable to have the dates paid arranged in year/calender order in the
Report Filter drop down menu is because I used a Text format for the dates paid column. Unfortunately, the person could not
suggest an alternative approach to help me achieve what I want to do.
If the problem arises from the inclusion of
the text 'Unpaid' in the column, and if there is any other way I can re-arrange the Data Table to include, and enable me to
also filter and analyse in the Pivot Table both the 'Paid' and 'Unpaid' invoices, I will be grateful also.
all for your anticipated help.