+ Reply to Thread
Results 1 to 6 of 6

PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

  1. #1
    Registered User
    Join Date
    06-14-2006
    Location
    Toronto, Canada
    Posts
    15

    PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

    I need to group sales records in a pivot table by our company’s fiscal year, fiscal quarter and fiscal month. Attached is a spreadsheet containing dates that need to be grouped. Ideally, the solution should be able to recognize future fiscal dates, too.

    Our fiscal months:
    - START: the first day (Saturday) after the last Friday each month (note: this Saturday can belong to the current or the next month)
    - END: the last Friday each month
    Example: May 26 2012 – June 29 2012

    Our fiscal years:
    - START: on the first day (Saturday) after the last Friday in April (note: Saturday can belong to May)
    - END: on the last Friday in April
    - Examples:
    o April 30 2011 – April 27 2012
    o April 28 2012 – April 26 2013

    Our fiscal Quarters:
    - Examples:
    o Quarter1: April 30 2011 – July 29 2011
    o Quarter2: July 30 2011 – October 28 2011
    o Quarter3: October 29 2011 – January 27 2012
    o Quarter4: January 28 2012 – Apr 27 2012

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

    Hi,

    Does this site help explain an answer. You can select start and end dates and number of days to group.
    http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-14-2006
    Location
    Toronto, Canada
    Posts
    15

    Re: PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

    Hello,
    thank you for your reply, MarvinP. Unfortunately, I didn't find a solution in the link suggested that would be applicable to my case.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

    OK - How about using a helper column for your special quarter dates. Then use a VLookup with a True range.

    See the attached with a helper column.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-14-2006
    Location
    Toronto, Canada
    Posts
    15

    Re: PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

    The solution works MarvinP; however, as I was working on it I've realized that a manual interaction would be required for the new records imported daily into the spreadsheet (the spreadsheet is linked with MS Access), meaning the formulas would need to be manually applied to all new entries. Unfortunately, I need to automate the process so I guess I would need to find a way to apply fiscal dates to each record in MS Access before they get into MS Excel.
    I'll change the post status to "Resolved"; I truly appreciate your assistance with this challenge, MarvinP.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month

    Hi,

    If it is in Access to begin with, why can't you build (in Access) a field to calculate the Quarter Field. Then output that field and use it in the Pivot.
    In Access I'm thinking a query using a "between" operator to calculate which quarter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1