Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date formats (2000)

    I have a large spreadsheet that includes a column that contains the date a transaction took place (mm/dd/yy). I want to include this col. in a pivot table, but I want the total of Jan-00, Feb-00 to present. Formatting the cells to "mmm/yy" doesn't work, I still get a column accross the top for each day a transaction took place. I also tried the MONTH and YEAR function to break it down but when I got to the Pivot Table the months went "1...10...11...12...2...3...4..." etc.
    How do I get the pivot table to "look" at the date as "mmm/yy" instead of "mm/dd/yy"?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date formats (2000)

    Create a new column in your data that refers to your date column (I will assume it is col D):
    This new column should have:
    =date(year(d1),month(d1),1)

    This will make ALL January dates in 2002 = Jan 1,2002, etc
    Do the Pivot table columns with this column and format it "mmm/yy"

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formats (2000)

    Thank you very much! Worked perfectly...I won't tip my hat to you because you might see the bald spot from where I was pulling my hair out.

    Stats <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formats (2000)

    Yet another approach (which we use where I work) is:

    1. Create new column.
    2. If your new column is E, E1 contains formula "=D1" (without the quotes).
    3. Format the new column as "mmm/yy".
    4. Copy--> Edit Paste Special--> Values.

    You'll get the same result for your pivot table.

    Finally, we also use VBA code to be sure the months appear in the correct order. Here's a sample of our code (keeping in mind that we call this field "Month" and we use only the first three letters of the month without the year):

    On Error Resume Next
    With .PivotFields("Month")
    .PivotItems("Jan").Position = 1
    .PivotItems("Feb").Position = 2
    .PivotItems("Mar").Position = 3
    .PivotItems("Apr").Position = 4
    .PivotItems("May").Position = 5
    .PivotItems("Jun").Position = 6
    .PivotItems("Jul").Position = 7
    .PivotItems("Aug").Position = 8
    .PivotItems("Sep").Position = 9
    .PivotItems("Oct").Position = 10
    .PivotItems("Nov").Position = 11
    .PivotItems("Dec").Position = 12
    End With
    On Error GoTo 0

    We need the error trapping because not all of our reports contain all the months.

    Hope this helps.

    Regards,

Posting Permissions

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