Date to Text (XL97-->)

[Jezza]

I have one of those irritating date format issues.

I have downloaded a series of work requests for a series of financial years. A column "Date Raised" is currently in DD/MM/YY format and I wanted to make a pivot table of the results for that monththe other field is an Area/Patch. I decided to create a new column called MonthYear so that I could produce this pivot table to break the job counts per month. After faffing about with copying and paste special and formatting it to date( didn't work) and then to text format (didn't work) I used =TEXT( A1, "mmm yy") this created the desired affect but when I created the pivot table I got the figures up correctly but of course it did it in alphabetical order not month order ie Apr 03, Aug 03, Dec 03, Feb 03.

Am I missing something here, any suggestions of a better method to extract this data

[HansV]

You could add a calculated column to the source data: =Date(Year(A1),Month(A1),1) etc., and format as mmm yy or whatever you prefer. Since these values are "real" dates, they will be sorted correctly.
Another option is to create the pivot table based on the raw data, then click on the Date Raised button, select Data | Group and Outline | Group... and specify that you want to group by month.

[Jezza]

Thanks Hans

Management Report now whizzing its way off to the Boss, perfect