itsmehar
asked on
Excel 2013 - Maintain Date formatting - Pivot to Graph
I am trying to maintain the date formatting between the pivot table and the corresponding graph.
1. In the pivot source data the field is populated using this formula: =DATEVALUE(TEXT(G2, "m/yyyy")) where G2 is a date field in mm/dd/yyyy hh:mm AP/PM format.
2. When i create a pivot table the "Month" column shows up as mm/01/YYYY and i can reformat it to MM-Y format but when i create a chart from it the y-axis shows up as mm/01/yyyy format and i cannot change it.
3. I am using Excel 2013
Does anyone know how to do this?
1. In the pivot source data the field is populated using this formula: =DATEVALUE(TEXT(G2, "m/yyyy")) where G2 is a date field in mm/dd/yyyy hh:mm AP/PM format.
2. When i create a pivot table the "Month" column shows up as mm/01/YYYY and i can reformat it to MM-Y format but when i create a chart from it the y-axis shows up as mm/01/yyyy format and i cannot change it.
3. I am using Excel 2013
Does anyone know how to do this?
ASKER
here you go ...
sample.xlsx
sample.xlsx
Since i didn't had the associated raw data i created one for my own reference..Now if you see sheet1..thats the raw-data and then on sheet3
I created two pivots one is by using the group by function of pivot table and then create a table and then second adding a formula in my raw data first and then using it which gives you the exact thing what you are looking for..
Again i was working on 2007 and you work on 2013 so you can change the graphs accordingly in 2013..
Saurabh...
Saurabh...
sample.xlsx
I created two pivots one is by using the group by function of pivot table and then create a table and then second adding a formula in my raw data first and then using it which gives you the exact thing what you are looking for..
Again i was working on 2007 and you work on 2013 so you can change the graphs accordingly in 2013..
Saurabh...
Saurabh...
sample.xlsx
Also i looked into your data and in your data you have a blank row in your raw data..Now if you delete that blank row and then play around with your dates it will behave the manner you want to as because one blank row entry..it's not recognize the entire column as date..
Saurabh...
Saurabh...
ASKER
Saurabh,
I am not sure what you are recommending, if i change the data set and it will obviously work. I was trying to figure out How to maintain the same formatting in the graph as the pivot table or at least change it in the graph itself.
I am not sure what you are recommending, if i change the data set and it will obviously work. I was trying to figure out How to maintain the same formatting in the graph as the pivot table or at least change it in the graph itself.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Saurabh...