1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 pivot table date format for chart

Discussion in 'Business Applications' started by StoryAngel, Jan 14, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. StoryAngel

    StoryAngel Thread Starter

    Joined:
    May 29, 2002
    Messages:
    101
    I have a pivot table in Excel 2007 with grouped data based on the year and the quarter, so the date info looks like this:
    Code:
    Years	Date
    2009	Qtr1
    	Qtr2
    	Qtr3
    	Qtr4
    2010	Qtr1
    	Qtr2
    	Qtr3
    	Qtr4
    2011	Qtr1
    (I know it's not really code but I didn't know how else to maintain the columns)
    I have a chart based on this data but I need the dates (on the horizontal axis) to appear in a very specific format of Q109, Q209 and so on. Is there a way I can either alter what's in the pivot table to achieve this, could I use this data combined with the TEXT function somewhere else on my worksheet, or is there another workaround?

    My source data is by month only and I'm using the pivot table to calculate the quarterly averages for me.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    I was thinking that the table you have based the picot table on, does this containt the column with Qtr1, Qrt2 etc?

    You could add an extra column beside it that would in clude the value as you like it to happen:

    Code:
         A                 B           C
    1   Date               Quarter     Formula
    2   01-01-2010       Qtr1        ="Q" & right(B2;1)  & right(Year(A2);2)   
    
    
    The result of the formula should be Q109

    Maybe that this idea helps?
    If it doen't then could you attach a sample sheet with non-sensitive data?
     
  3. StoryAngel

    StoryAngel Thread Starter

    Joined:
    May 29, 2002
    Messages:
    101
    Hoi Keebellah,
    I've benefited from your help before! :)
    This is what the pivot table looks like (with sanitized data):

    Code:
    Years	Date	Average
    2009	Qtr1	105
    	Qtr2	161
    	Qtr3	120
    	Qtr4	133
    2010	Qtr1	119
    	Qtr2	126
    	Qtr3	131
    	Qtr4	152
    2011	Qtr1	90
    My original data (on which the table is built) is in two columns - Date in column A (mm-yy) and number in column B. I'm using the pivot table function to calculate the quarterly average figure for me, by grouping it by year/quarter and showing the average. You might remember helping building me a handy macro that did this a short while ago, which functions great... except that I need to be able to tweak it and use in multiple situations, and I don't know VB so I looked for another way to solve the problem (one that I could manipulate without needing to learn VB, hehe). The pivot table does a fine job of calculating the quarterly averages, but by grouping the data, there are "gaps" in the Years column for quarters 2-4 as shown here. Useful if you want to expand/collapse the data, but not so handy if you don't want gaps in your column! :)

    Your suggestion for building a text formula in that way works great for quarter 1 in each case; I'm just not sure how I'd derive it for all quarters if I base it on the pivot table. If I could figure out how to derive the year for Q2-4 then that would solve the issue nicely. I think I'd need to create another column outside the pivot table but I'm not sure what would need to go in there.

    Thanks for your help - I really appreciate it!
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    What you should do is add a column to your original data sheet and build the Pivot table with this new column

    See attached example
     

    Attached Files:

  5. StoryAngel

    StoryAngel Thread Starter

    Joined:
    May 29, 2002
    Messages:
    101
    Keebellah,
    After a bit more tweaking this is now working for me. I still have a lot to learn about pivot tables but you've really helped me. Hartelijk bedankt! Much appreciated :)
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
  7. StoryAngel

    StoryAngel Thread Starter

    Joined:
    May 29, 2002
    Messages:
    101
    Aww thanks! Great suggestion! :D
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974735

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice