Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date to Text (XL97-->)

    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
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date to Text (XL97-->)

    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.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date to Text (XL97-->)

    Thanks Hans

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •