Regional/International Date Issues!! mm/dd/yyy vs. dd/mm/yyyy

[KyleHansen]

Hey all - this one has been driving me nuts, and I'm sure there's an easy fix to it but I'm not sure what it is -

I've set my date tables up per the first table below, but when I link the data table, PowerPivot recognizes the date in a different format. The Excel date table is in the same format that my fact table is imported (mm/dd/yyyy).

Excel Date Table (sorted oldest to newest):
01/02/2013 01 January
01/03/2013 01 January
01/04/2013 01 January
01/05/2013 01 January
01/06/2013 01 January
01/07/2013 01 January
01/08/2013 01 January
01/09/2013 01 January
01/10/2013 01 January
01/11/2013 01 January
01/12/2013 01 January
01/13/2013 01 January
01/14/2013 01 January
01/15/2013 01 January
01/16/2013 01 January
01/17/2013 01 January


Date Table on Import (sorted oldest to newest):
01/02/2013 12:00:00 AM 02 February
01/03/2013 12:00:00 AM 03 March
01/04/2013 12:00:00 AM 04 April
01/05/2013 12:00:00 AM 05 May
01/06/2013 12:00:00 AM 06 June
01/07/2013 12:00:00 AM 07 July
01/08/2013 12:00:00 AM 08 August
01/09/2013 12:00:00 AM 09 September
01/10/2013 12:00:00 AM 10 October
01/11/2013 12:00:00 AM 11 November
01/12/2013 12:00:00 AM 12 December
01/13/2013 12:00:00 AM 01 January

 

[shg]

Are those Excel dates in a single column with peculiar formatting applied? They don't look like dates.

 

[KyleHansen]

Nope, they're individual columns separated here -

01/02/2013 / 01 / January
01/02/2013 12:00:00 AM / 02 /February

I changed the date/time format on my computer, and it fixed the formatting in the date tables, however, now for the sales table by month, it's showing a value in September 2016 - when I click on the value, it's pulling sales from Feb 9, 2016 (09/02/2016
is being recognized as September 2016).

 

[shg]

Format as dd mmm yyyy and you'll see how your 'puter is interpreting them.

If they are backwards, Data > Text to columns > Next > Next, tick Date, select either MDY or DMY as appropriate, Finish.