Date formats vary between countries. If you need to work with external source reports from a different country, you may see that Excel doesn't recognize the dates or recognize them wrongly. The most common case happens between US and European countries because of the placement of month and day figures. In this guide, we’re going to show you How to convert European date to US date in Excel and vice versa.
The problem
While the United States uses the date format M/D/Y, most countries including European accepts the D/M/Y format as standard. Thus, if you haven't manually selected an opposing format deliberately, your computer runs on the locale format as well.
This difference may cause problems if you are using data from outside of Excel like CSV files or another software which keeps dates as strings.
For example, the data in the following image has been imported from a CSV file that was including Euro-based dates. Eventually, Excel couldn't recognize them as dates:
- Cell format is "General"
- YEAR function returns #VALUE! error due to an invalid date.
Let's continue with how you can convert European date to US date without hassling with bunch of formulas.
If you are just exchanging Excel files (xlsx, xlsm, etc.), you are good due to Excel's date-storing approach.
Power Query
Excel's Power Query is a data maintaining and reshaping tool that you have under your hands. The Power Query has been released as an add-in for Excel's 2010 and 2013 versions. Since Excel 2016, it has been a built-in feature you can find under the Data tab of the Ribbon.
While we are writing this article, the buttons are placed in the sections named Get & Transform Data and Queries & Connection.
Note: You can learn more about Power Queries in Power Query 101 article.
Converting European date to US date in Excel
After instructions are made, we are ready to action. First, you need to parse your data with Power Query. Use the command in Get & Transform Data section corresponding to your source data. For example, use From Text/CSV if your data is in a text-based file or From Table/Range if you copied and pasted values to your worksheet directly.
While importing external data you will see the preview window. If so, click the Transform Data button to open the Power Query window.
Power Query will be open in a separate window with data manipulating tools.
Click ABC123 icon at the header of the invalid date column.
Click on Using Locale.
After a few seconds, a new dialog will pop up with Data Type and Locale selections. Select a date-based option that fits your data. It's only Date for ours.
Select the origin of your dates in Locale dropdown. We selected "English (United Kingdom)" according to our data.
After clicking OK, you will see that all the dates are formatted to your locale correctly.
After, you have satisfied with your dates and the remaining data, click on Close & Load in the Home tab to populate the valid data into your worksheet.
The data will be populated on a new sheet. Of course, you can move it if you need.
