Pivot Table ambiguity

[Isskint]

I have 2 pivot tables derived from the same set of data. Pivot1 is an overview of performance per person, per work category. Pivot2 is an individual report. Both pivots have a Page selection on Date. The ambiguity comes on the individual pivot where the data is split (no consistancy) between dd/mm/yyyy and mm/dd/yyyy, even though Pivot 1 only shows dd/mm/yyyy. The data is purely dd/mm/yyyy. The effect of this is that indivdual performance can not be easily checked without cross referencing the 2 reports.
I have tried all the usual date tricks (even converted dates into text strings so they are not seen as dates) but it keeps happening. I could understand on days less than 13, but even tonight i am getting 11/21/2012 and 21/11/2012.

Does anyone have any idea why this happens?

 

[pr2-eugin]

Hello Mark, Did you try changing the Format of Pivot2? Since I could read the data is stored right.. Try changing the Format,

 

[Isskint]

Thanks for that Paul. The format is set as required.
The thing i can not get my head around is why the list of available parameters in the Page tab for date shows the same date in 2 formats AND has seperate data against each date format even though the pivotdata source is purely in the 1 format.

As a shortcut for the users, I have written a little VBA to set the Individual Report parameters (Name and Date) when a name is double clicked. It is simply date pivot2 = date pivot1. Could this be the source?

 

[pr2-eugin]

Sorry I am not of much help here.. I am not really great with Pivots, as my limited usage of this functionality.. That sure is a bit bizarre.. But I am puzzled with the 'data' in the same source is a bit twisted..

 

[Isskint]

Ok there seems to be some connection between this and the VBA code. The attached is 2 screen shots before and after the code is run and you will see good date for 22/11/12 before but both 11/22/12 and 22/11/12 after. it makes no sense:banghead::banghead:

 

[NBVC]

It might help if you attach the workbook, replacing any confidential data.

Are you sure about the dates in the source?

Try selecting the column of dates in the source data, then go to Data|Text to Columns, skip to 3rd dialogue and in the column data format area, select Date, then select MDY in the adjacent drop down box.

Click Finish. Then refresh the table. Is it fixed?

 

[Isskint]

NBVC, thankyou for your suggestion. I tried the Text to Columns but it has not made any difference.

Spreadsheet attached. The amount of raw data has been drastically reduced to get the file small enough to upload. Normally you would be looking at 30K - 60K rows, but i have already spotted a dodgy date 02/10/12 and 10/2/12.
The first tab "T1 Picked Shipped by Part" is the raw data imported into the spreadsheet from an ODBC connection to a server database/WMS. I have removed the link and given a snapshot of data.
"BVL charts" is a pivot table showing pick per category and a graphical representation. Also a VBA generated table and graph of the same data in a different format.
"IndRep" is the last tab and shows the snapshot data set for a single picker. This can be selected by the user by double clicking the picker name in column L of BVL Charts. This is when a dodgy date will show up. If you check the available dates on IndRep prior to double clicking a name, you will see normal dates, but after you often find the dodgy date.

I appreciate any guidance you can provide on this issue (or anything else that you see)

 

[NBVC]

The dates in the Date Picked column of the database sheet are text, since you use the function TEXT(). You need to either convert that to a real date by adding a +0 to the end of the formula, or changing the formula to the more appropriate: =INT(S5) copied down. Either way, you will need to then format the column as DATE

Then refresh the Pivot table in BVL Charts so that the Date Picked drop down is updated.. and now you can double-click a name and see if it all works out....

 

[Isskint]

Many thanks NBVC, i have used INT() and it now works ok. Strangely it converts the date format to m/d/yy AFTER pulling up an independant report, but at least all the data from the selected date is now displayed, instead of in 2 sets.