Tech Support Guy banner
Status
Not open for further replies.
1 - 3 of 3 Posts

· Registered
Joined
·
1 Posts
Discussion Starter · #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)
 

· Registered
Joined
·
589 Posts
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
 

· Registered
Joined
·
22 Posts
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
 
1 - 3 of 3 Posts
Status
Not open for further replies.
Top