I'm having problems with Tableau reading dates correctly from an imported spreadsheet. Attached is an extract of two spreadsheets, one which behaves as expected and one which does not and also a packaged workbook containing this sample. My original workbook contains some sensitive information which is why I've cut it down to the basics of these two sheets.
One sheet has dates taken from various days during the month over the year (eg 5th of February, 17th of February etc) the other has dates from the first of each month over the year (eg 1st of November, 1st of December etc.)
When I import these spreadsheets, pivot them based on the dates and set the resulting pivot field to date format; the spreadsheet containing multiple days of the month maintains the UK date format and displays correctly on a line chart. However the second spreadsheet is converted to the US format and when plotted on the line chart all the points appear over the first twelve days of January of 2017 and then the first nine days of January 2018.
My workbook locale is set to the UK. I tried leaving the pivot field as a string and used the DATE([Pivot Field Names]) and DATEPARSE('dd/mm/yyyy',[Pivot Field Names]) to see if that would act as a work around but it did not. I may not have used DATEPARSE correctly as it plotted them all on the 1st of January but at different times, as seen in the workbook.
The attached workbook contains each spreadsheet inserted three times but each is at a different stage if you want to check the imported data. The first instance of each spreadsheet has no additional changes to the data. The second shows both spreadsheets after pivoting. The third has the pivot field set to date where you can see the dates switch to the US format on one of the spreadsheets but not the other.
Any help with this would be greatly appreciated.