How to change the date format?

[sanchit]

Hi,

For Data: I have imported a .CSV file.

I have two questions?

1. My Date is in format 1/3/2016 7:56:00 AM (DD/MM/YYYY hh:mm:ss), but PowerBI is reading the Date as MM/DD/YYYY hh:mm:ss.

         How can I change the date format to the desired format?

     2.Another column has date in DD/MM/YYYY (27/08/2016) format but PowerBI is reading it as Text.

•  While trying to convert it into required format, from Modelling->Data Type in the Report Window, it gives an error “We can’t automatically convert the column to Data Type.”
• While trying to convert it from Edit Queries -> Transform, output is Error in each cell.

        Some cells have blank data.

This is my first question, please pardon me for the language.

Thank you for the help.

[nohowUp_fnDown]

Why is something as simple as formatting a date so hard in Power BI? Is it that Microsoft is focussing all their development effort on the data engine/ DAX and zero on the development tool? Why do I even have to search "How to format a date in Power BI"? I've been doing this for decades. 

[vinayjayappa]

How do we change the format of the column header to date format and remaining data to be in number format?

Thanks

Vinay

[Sean · Accepted solution]

@sanchit This is controlled by the Locale Setting!

MM/DD/YYYY is the US format (and maybe some other countires not sure???)

But your csv file likely came form a place where they use DD/MM/YYYY

To have PBI recognize those as dates Go to File => Options and settings =>  Options => Locale => change to your country/region

[sanchit]

@Sean Thank you so much  It worked. 

I was trying it with DAX but in vain , is there a way to solve it using DAX , manually adding a new column with a different date format.

[geejaysantiago]

hi @sanchit what locale setting did you use? i need the dd/mm/yyyy option as well

[Sean]

@sanchit Probably easiest way would be in the Query Editor

Split Column => by delimiter => Custom "/" at each occurence

And then Merge those columns in the "correct" order.

That should work!

[sanchit]

@Sean I will try that . Thanks

[Sean]

@sanchit Here are the steps to convert DD/MM/YYYY to MM/DD/YYYY

[zthurman]

This worked for me! I guess I'm just confused that in my queries that I'm using as my data source I can pull my dates however I want with: convert(date, cast(sot.closeddate as date), 110)

When I pull them into a graph it gets formatted how I think it should, mm/dd/yyyy.

But when I pull them into just a plain table, it either comes up as a datetime, or if I change the type in the date column to Date inside the query editor it comes into the table as 'Friday, October 20, 2017' in the table columns.

It's a little frustrating that I have to then go through and say 'Hey PowerBI, even though I've already been very explicit with how I want this date formatted in TSQL when I tell you to go out and get the data, I still have to split and remerge my date columns as text fields based on my '/' delimiter.

Things that make you go: (/ .□.)\ ︵╰(゜Д゜)╯︵ /(.□. \)

[zthurman]

This worked for me! I guess I'm just confused that in my queries that I'm using as my data source I can pull my dates however I want with: convert(date, cast(sot.closeddate as date), 110)

When I pull them into a graph it gets formatted how I think it should, mm/dd/yyyy.

But when I pull them into just a plain table, it either comes up as a datetime, or if I change the type in the date column to Date inside the query editor it comes into the table as 'Friday, October 20, 2017' in the table columns.

It's a little frustrating that I have to then go through and say 'Hey PowerBI, even though I've already been very explicit with how I want this date formatted in TSQL when I tell you to go out and get the data, I still have to split and remerge my date columns as text fields based on my '/' delimiter.

Things that make you go: (/ .□.)\ ︵╰(゜Д゜)╯︵ /(.□. \)

[baptiste]

Another way of doing:

In Power Query Editor, right-click on the column header / "Change Type" / "Using Locale..." / then choose Date format with the locale you want

source:

https://community.powerbi.com/t5/Desktop/date-format-is-changed-in-power-bi-desktop/m-p/45385#M17618

[hellovaras]

Hello,

Tried the "Change Type" using "Locale" but I get errors. It seems all the errors happen for the "01", January. Please see below. How to fix this issue?

Error message:

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    25/01/2005

[amk27]

You can also alternatively try this as a new column, adjust the sequence of first and second expression depending upon the date format that you have and concat them in the new column.

List.First(List.RemoveFirstN(Splitter.SplitTextByDelimiter("/")([Date]),1)) -> Evaluates second number in the Date value

List.First(Splitter.SplitTextByDelimiter("/")([Date])) -> Evaluates first number in the date value

List.Last(Splitter.SplitTextByDelimiter("/")([Date])) -> Evaluates year in the date value

[vinayjayappa]

How do we change only the column header to date format and the data to be in the number format?

Many thanks

Vinay