Excel 2007 - Change date format for pivot tables

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.

NCFC

Thread Starter
Joined
Sep 17, 2008
Messages
1
Hi

Can someone show me how to covert a date e.g. dd/mm/yyyy so that it reads as mm/yyyy only and so that a pivot table doesn't recognise the orignal long date of dd/mm/yyyy.

Tried using custom to mm/yyyy but pivot table still reads the date as dd/mm/yyyy.

Thanks

NCFC (Newbie)
 
Joined
Dec 9, 2003
Messages
563
hi NCFC,

Don't know about Excel 2007, but in 2003:

Right Click the Pivot Table,
Field Settings,
Select the field name that holds the dates,
Number,
Choose your format.

lol
Hew
 
Joined
Sep 18, 2008
Messages
22
Hi NCFC,

I have this problem a lot and found the best solution is to create two additional cols in the base data. Using example date 19/09/2008, first col formula: =month(a2) this returns '09'
2nd addition col =year(a2) this returns '2008' or '08'

You can then dray the year and month into the pivot. I find year first is best if your date range is spans years.

Regards - Nic
 
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

Members online

Top