Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display date values in pivot table data field

    Could you provide more info about the source table, or attach a (stripped down) sample workbook?

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    display date values in pivot table data field

    I have a pivot table with customers in rows and items in columns, but i need to display the last date of purchase of the item or maximum as the date parameter, but all i get is 1900-01-01 after going in the file options and and selecting the date format

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: display date values in pivot table data field

    here goes
    Attached Files Attached Files

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

    Re: display date values in pivot table data field

    The values in the Date PSSE column on the source sheet are not date values but text values, as you can see by their being left-aligned. You can correct this as follows:
    - Select an empty cell in the Date PSSE column.
    - Copy it to the clipboard (Ctrl+C).
    - Select the cells with dates.
    - Select Edit | Paste Special...
    - Select the Add option and click OK.
    Now update the pivot table and format the data item as a date.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: display date values in pivot table data field

    it works, but I would like to know how you would have known this trick.

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

    Re: display date values in pivot table data field

    Problems like this (although not with pivot tables) have been mentioned here and on other websites many times, so when I saw that the maximum value was 0 throughout, I suspected that there was something wrong with the source data. The left alignment was a giveaway.
    The trick of using Paste Special to add a blank cell has also been described here often.

Posting Permissions

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