Solved: Excel 2007 pivot table date format for chart

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

StoryAngel

Thread Starter
Joined
May 29, 2002
Messages
102
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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?
 

StoryAngel

Thread Starter
Joined
May 29, 2002
Messages
102
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!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Attachments

StoryAngel

Thread Starter
Joined
May 29, 2002
Messages
102
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 :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top