Help with formating dates

James maverick

New Member
Joined
Jun 25, 2010
Messages
9
Hello everyone,

Good day, I'm trying to come up with a graph based on a pivot table, one of the issues i am having is with the dates.

on column a i have the dates as per normal:
1/09/2009
2/09/2009
3/09/2009 and so on and so forth

i have then formated the dates to show MM-YY
Sep - 09
Sep -09
Sep 09

Problem is, with the pivot table it still is broken down per day, so for example i have got three rows of Sep - 09, when all i want is i row for that month, and another row for another month...so the graph would be monthly and not daily.

help please

regards,

James
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
hi, formatting the dates in a cell wont change the underlying data, i.e. the numbers that sit in those cells. to excel, they will still be sep 1, sep 2, etc.

to have excel read the dates as mm-yy, you can create a helper column with =month(A1) & "-" & year(A1) kinda formula.
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Hello James,

You can group your pivot table's date field. Right click on the "date" header in the pivot table and select "group," then group it by month. Hope that helps.

Tai
 

James maverick

New Member
Joined
Jun 25, 2010
Messages
9
thanks guys, i forgot to mention that the report is for 4 years worth, so i was hoping to avoid grouping the whole report manually, if there was an alternative to doing it "automatically" with a formula of some sort
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Post #2 is a formula you could copy down on your source data - post #3 is a method that involves three or four clicks to turn grouping on or off on your pivot table; neither is manual :)
 

James maverick

New Member
Joined
Jun 25, 2010
Messages
9
magic!! thanks guys, i only knew how to group it manually, buy selecting the cells, instead of the header (duh?? ) preciate all the help..till next time

regards,

James
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top