Date format in Pivot Tables
I have several Pivot Tables using the same data source in XL. The different tables interpret the dates differently. In one table it is shown as 2013-12 and in another table it is shown as 2013-12-31. I have tried to make sure that the format is the same in both tables and in the source, but still it is presented differently. What could possibly be wrong=
Table 1:
Row Labels
2013-10
2013-11
2013-12
2014-01-31
Grand Total
Table 2:
Row Labels
2013-10
2013-11
2013-12-31
2014-01-31
Grand Total
Are there different cell formats applied?
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
Thanks for your reply.
I have marked the entire column in the source data sheet, and made sure the date format is YYYY-MM-DD, but then the Pivot Table still interprets the dates differently as shown above.
Hi MaximusXL and welcome to the forum,
I'd bet some of your dates are really text. Select the entire column of your dates (in the data) and show them as numbers. See which don't convert and then you'll know which are text instead of dates...
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks for your suggestion.
It sounded like a good suggestion, but when a convert the entire column in my source data to numbers - all of them show up as numbers. And when I update the Pivot Tables it shows as numbers (which it should), but when I convert it back to dates, it shows up as before (above)...
Have you tested number format on pivot table sheet?
Yes, that one was very intersting. Here it seems like only the dates shown as YYYY-MM-DD are converted to numbers. The other dates seem to be text formated. This is definitely a lead, but I can not understand how I should make them all in the same format...
You have refreshed pivot table and verified source data is range you are looking at?
Yes, I have refreshed pivot tables and checked source data range.
May you post sample workbook?
I have remeved most of the material, but kept the part that I have problems with. I think it should be clear...
There is one sheet with data, 2 sheets with Pivot Tables, and 2 sheets with corresponding charts.
Slight differant approach.
Why not use the month and year (and day) in differant cells.
Than you also be able to compare differant criteria (e.g. months).
See the attached example.
I changed some data (marked with the bleu backgroundcolor).
I add to columns (marked with the yellow backgroundcolor).
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thanks for your suggestion.
Yes, I am sure that would work. My problem is that I have Macros importing and exporting data to and from this file. And I really do not like to rewrite the code. If nothing else works, I may do as you suggest, but I would prefer to understand the date format, if possible...
This is strange workbook. If I change pivot tables to not remember missing information and delete old dates, refresh, then put back old dates and refresh, everything is formatted correctly.
This last comment sonds very interesting!
But how do you "change pivot tables to not remember missing information"?
And when you "delete old dates" - did you delete them in the source data or in the Pivot Table?
You right-click the pivot table, select Pivot Table options, select Data tab and change 'Number of items to retain per field' setting to 'None'. Data was changed in source data.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks