Date Formatting - Excel to Power BI (FAIL)

[Anonymous]

In this scenario - all dates occur in the month of June 2021.

In Excel: I define a column data type as being a date. I define that date sequence as being dd/mm/yyyy

In Power BI: I import the data from excel and ensure the formatting has pulled over correctly, and matches. 

Power BI fails to understand date formatting - all dates should show in june. 

Power BI trades the day for the month. 

[Anonymous · Accepted solution]

I resolved the problem - the date formatting I was using in Excel was incorrect. I used the "Text to Columns" tool to fix the problem. 

Apparently using the Data Type selector in Excel has no effect if the cells come from a csv file.  

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, I have created a Date column in Excel, and using the date format in English(Canada):

After connectng and transform the table in Power BI, the data type was specified as Text. Because the default locale of my Power BI  is English(United States) in which date format is mm/dd/yyyy:

So in this case, please right-click the column --> Change Type, select "Using locale", then the type will be changed to Date successfully.

At last, apply all changes--> Click the Date column in Field pane--> Select Column tools-->Format it as dd/mm/yyyy:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yikes. If modern software/systems need all these steps to understand a date that's a mark of poor design. 

Thanks for your help. 

[Anonymous · Accepted solution]

I resolved the problem - the date formatting I was using in Excel was incorrect. I used the "Text to Columns" tool to fix the problem. 

Apparently using the Data Type selector in Excel has no effect if the cells come from a csv file.  

[Vera_33]

Hi @Anonymous 

You can use Date instead of Date Hierachy, check the field

[Anonymous]

Yes, I've tried this but it doesn't work when filtering. The dates will display as intended, but they don't filter propoerly. 

[Vera_33]

Hi @Anonymous 

What do you mean by it not working when filtering? How did you filter? Filter Pane, slicer? Can you provide examples?

[Anonymous]

Filtering... as in using a slicer, filter pane... the problem is not the method of sorting, the problem is Power BI confusing dd/mm/yyyy with mm/dd/yyyy. In the exmples provided I have predefined how the data is laid-out in Excel, but when importing that data Power BI reads it backwards. 

I solved the problem by reversing the mm/dd... but it begs the question as to why the formatting feature in excel even exists at all. 

The problem: the steps outlined in this article have no effect on date formats when they carry over to Power BI... 

Format a date the way you want - Office Support (microsoft.com)