Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Grouping (2003)

    Is it possible to change the Pivot Table grouping for "Quarters" so that Q1 begins November 1, Year 0 and ends Jan 31, Year 1; Q2 begins February 1, Year 1 and ends April 30, Year 1, Q3 begins May 1, Year 1 and ends July 31, Year 1 and Q4 begins August 1, Year and ends October 31, Year 1?

    Thank you, Dr. Andy

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

    Re: Pivot Table Grouping (2003)

    You can add calculated columns to your source data.

    If you have installed the Analysis ToolPak, you can use the EOMONTH function.
    Let's say you have dates in column A, starting in A2 (A1 is the field name)
    In a column directly adjacent to your source data, enter a suitable field name in row 1.
    Enter the following formula in row 2:
    <code>
    =EOMONTH(A2,2)
    </code>
    This will return the last data of the month two months after the date in column A.
    Fill down as far as needed.
    Use this calculated date in your pivot table instead of the original date, and group by quarter.

    If you don't want to use the Analysis ToolPak, you can use this formula instead:
    <code>
    =DATE(YEAR(A2),MONTH(A2)+3,0)
    </code>
    It returns the same value as the one above.

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Grouping (2003)

    Hans,

    Excellent. Much more straight forward to change the data configuration than to try to change the program. I must think of that approach more often.

    Thank you.

    Dr. Andy

Posting Permissions

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