Forum Discussion
Date Formats
I'm in the UK
I have a field that I'm importing as YYYYMMDD.
I'm writting reports that are mainly used in the US so I created a date field using:
DATEVALUE(mid(vw_PowerBI_Analysis[OrderCreateDate],5,2)&"/"&right(vw_PowerBI_Analysis[OrderCreateDate],2)&"/"&left(vw_PowerBI_Analysis[OrderCreateDate],4)
Which should have givien me mm/dd/yyyy
However in my model the the date is displaied as dd/mm/yyyy and when a US user views the published dashboard the dates continue to be dd/mm/yyyy.
Is there a way to have the date respect the user locale?
Thanks,
Dan
6 Replies
You can do this in the query editor. For each date column complete the following steps:
Remove the existing Change Type step for the date.
Right click the date column, select change type, select using locale (at the bottom)
Change the data type to date
Change the Locale to English (United States).
You can also choose other formats from here as well. I would recommend duplicating the column and leaving both date types if the reports are used "at home" as well.
In other words, have a US Date column and a UK date column for users to access.
Well, that seems to be a bit of a problem. Here is the link to the article where I learned to fix my date issues. It is the same method but tells you how to change the source to make it correctible as well.
http://www.excelguru.ca/blog/2015/07/08/fix-date-errors/
If that doesn't work, Ken offers further methods for difficult dates in another article written a few days later.
http://www.excelguru.ca/blog/2014/06/23/importing-dates-in-power-query/
If that doesn't help, Ken has a forum for questions as well. In desperate times, I turn there for help myself. He is very helpful.
