Ordering of year / month in a pivot table 2

[TobyA]

Hi,

I have a spreadsheet containing details of calls we've received. I need to show calls per week and month.

The date of the call is in A2. I've used =YEAR(A2) and =MONTH(A2) to get the year and the month from the date, then put them together using =(C2)&" "&(D2)

This data then goes into a pivot table. However, the pivot table does not display the months in the correct order because they are formatted "2007 2" rather than "2007 02".

Can anyone tell me how to format the month like "02"?

Hope this makes sense!

Cheers,
Toby

 

[xlbo]

try this:

=C2 & " " & rept("0",2-len(D2)) & D2


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TobyA]

Hi Geoff,

That's just what I needed.

I don't understand it, but it works perfectly :->


Thanks,
Toby

 

[xlbo]

The "Rept" function repeats a text string for a specified number of occurances. This is calulted by using the length of the data in D2 and subtracting that from the max length of 2. If there are already 2 digits, there will be no repeating zeroes. If there is 1 digit, it will put a 0 in front of the value in D2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

If A1 is a DATE...
[tt]
=TEXT(A1,"yyyy mm")
[/tt]
Also try just bringing the DATE into the PT. Then use Group & Outline on the Date field, selecting Month & Year.

Skip,

_[red][/red]

 

[TobyA]

Thanks a lot Skip.

Will try that also

 

[Gavona]

Wow, didn't know group and outlin could do that. Is this a new(ish) feature or have I just missed it?
Have a star.


Gavin

 

[SkipVought]

Cool, huh!

Caveat: If you have an EMPTY cell or an ERROR, Group will not work.

Getting rid of the offending values alone will NOT solve the problem. You must also assign the MissingItemsLimit of the PivotCache object to xlMissingItemsNone and then Refresh.

Skip,

_[red][/red]