Date format issues

[Anonymous]

I have source data files in CSV and in Excel format. CSV date format is mm-dd-yyyy and Excel is dd-mmm-yyyy.

I use "en-US" in the column conversion to datetime step for the CSV import, none for the Excel import.

PowerBI regional setting is Spain, international sort, and my Windows is in English but with Spanish locale.

In PowerBI desktop I format the date columns as dd/mm/yyyy and all display consistently in this format.

After publishing, the dates converted from Excel show in mm-dd-yyyy format in the service, but the ones from CSV data source stay in dd-mm-yyyy, as well as any calculated date columns. I tried many things (regional settings, culture, browser preferences) to no avail. Setting the PC to en-US of course does solve it, but that's not a solution, my PC needs to be in en-ES for my work. Formatting the date as text "mm/dd/yyyy" as well works, but then you can't sort properly on that date column.

So finally I will likely switch all dates to dd-mmm-yyyy (18-nov-2020) to avoid any ambiguity. However, I came across a very interesting detail, is this a bug or by design? If by design, it might actually help solve my problem, I just can't yet figure out how.

Calculated column 

Event End Date = Events[End Date]

Result:

Why is it converting?

[Anonymous · Accepted solution]

@amitchandak thanks for the quick response. This was indeed the case, no matter if from CSV in mm/dd/yyyy format or from excel in dd-mmm-yyyy format, in PowerBI Desktop they all showed as dd/mm/yyyy format based on my system (regional settings), without me touching anything.

However when published, some of them (the ones originating from Excel sheet and my calculated columns) displayed in mm/dd/yyyy while the ones originating from the CSV files showed as dd/mm/yyyy in the service.

Reading through various posts in this community I didn't get any closer to a solution, but now I realized that in this field

You're not limited to what is available in the list. Which makes me think that a lot of people don't know this either, as in may posts they state that they can't select what they need.

But as a matter of fact, you can just type the desired format string in the box!

[Anonymous · Accepted solution]

Even though Events[End Date] is a date column, the calculated column does not inherit that property, it is of type text and displays the data as it's stored (mm/dd/yyyy) rather than how it is displayed in the date column.

[amitchandak]

@Anonymous , ideally all date columns unless a format is chosen, should show date in the same format( System date format)

For the Power BI service, there is an option to change the format using the browser setting.  But all dates unless you have choose a format , should show same format

[Anonymous · Accepted solution]

@amitchandak thanks for the quick response. This was indeed the case, no matter if from CSV in mm/dd/yyyy format or from excel in dd-mmm-yyyy format, in PowerBI Desktop they all showed as dd/mm/yyyy format based on my system (regional settings), without me touching anything.

However when published, some of them (the ones originating from Excel sheet and my calculated columns) displayed in mm/dd/yyyy while the ones originating from the CSV files showed as dd/mm/yyyy in the service.

Reading through various posts in this community I didn't get any closer to a solution, but now I realized that in this field

You're not limited to what is available in the list. Which makes me think that a lot of people don't know this either, as in may posts they state that they can't select what they need.

But as a matter of fact, you can just type the desired format string in the box!