Help with formating dates

[James maverick]

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

 

[sulakvea]

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]

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]

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]

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]

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