Forum Discussion
Date Not Converting from UK to US
rbrickman23 Right-click the column header. Change type, From Locale choose Date and choose UK English
Hi rbrickman23 ,
Greg_Deckler Nice Answer! And rbrickman23 I'll try to explain in more detail what this function does for you.
The effect of Using Locale is to automatically modify the data types of the data columns based on the format of the locale you select, rather than changing the format of your date display to the format of the locale you select.
For example, if you select "Date and US English" in Using Locale, it means that you let Power Query automatically determine what the year, month, and day of the date you enter is in US format (MM/DD/YYYYY). While the date you entered is in UK format (DD/MM/YYYYY), the month can only be 1-12 but the day can be more than 12, and when the system reads a date with a day greater than 12, it will report an error (because the system thinks it is the month).
So just as Greg_Deckler said, choose "Date and UK English" in Using Locale:
Here is my sample data:
This is my region:Change the data type and the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.
Hi rbrickman23 ,
Greg_Deckler Nice Answer! And rbrickman23 I'll try to explain in more detail what this function does for you.
The effect of Using Locale is to automatically modify the data types of the data columns based on the format of the locale you select, rather than changing the format of your date display to the format of the locale you select.
For example, if you select "Date and US English" in Using Locale, it means that you let Power Query automatically determine what the year, month, and day of the date you enter is in US format (MM/DD/YYYYY). While the date you entered is in UK format (DD/MM/YYYYY), the month can only be 1-12 but the day can be more than 12, and when the system reads a date with a day greater than 12, it will report an error (because the system thinks it is the month).
So just as Greg_Deckler said, choose "Date and UK English" in Using Locale:
Here is my sample data:
This is my region:
Change the data type and the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.
Hello. I appreciate the explanation and it makes sense. I followed your instructions and it worked, it changed it to English (US) format. Thank you both!