Linked table in PowerPivot reading dates incorrectly

[mild.backspace]

Hi,
Hoping someone can help with the following.

I am reading the book "Pivot Table Data Crunching 2010" chapter 10 "Mashing Up Data with PowerPivot" page 216, section "Using RELATED() to Base a Column Calculation on Another Table"

As per the book the sample file is called "Ch10WeatherMashup.xlsx" but the actual file is called "PTChapter1025.xlsx"

Problem
1. open file "PTChapter1025.xlsx" --> Sales, click on any cell in the date column
2. click on "PowerPivot" --> Excel Data --> Create Linked Table
3. New PowerPivot table created with incorrect dates. The first 12 rows have a date format of mm/dd/yyy and then suddenly at row 13 the format changes to dd/mm/yyyy.

01/01/2006
01/02/2006
01/03/2006
01/04/2006
01/05/2006
01/06/2006
01/07/2006
01/08/2006
01/09/2006
01/10/2006
01/11/2006
01/12/2006
13/01/2006
14/01/2006

I have no idea why this is happening. Any suggestion or thoughts?

Regards,
Backspace Mild

 

[Derek Brown]

Hello and welcome to The Board.
My guess is that the problem occurs because when you try to create a linked table using the sample file, you get the warning that there is already a table linked to Table 1 - and PowerPivot is getting confused because that 'already linked' table was created on a computer using US dates?
I may be wrong in assuming that it remembers the earlier formatting but I found that if I copy the Sales data to another workbook and then follow the same actions, the dates then appear correctly.
So whereas following the example may give the problem, when you later use it 'for real' it should be OK?
Or perhaps there should have been the "Ch10WeatherMashup.xlsx" file which would not have given this problem?

 

[mild.backspace]

Hi Derek,
Thank you for taking the time to respond.

I took the easy way out, copy pasted into another excel file and was done with it *smile*

Regards,
Backspace