How to convert European date to US date in Excel with formula

You might have already seen that Excel's date-related functions return errors or incorrect values with external data. The most common reason is the difference between date formats, such as reverse day and month figures. Usual scenario between US and European countries. In this guide, we’re going to show you How to convert European date to US date in Excel with formula.

Download Workbook

Tip before starting

Unless you are not bound to formulas or want to learn formula way intentionally, check out our first guide with Power Query. The Power Query's Locale function turns entire process into a few clicks.

If you came from the Power Query article to here, you can skip the next section and continue.

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.

Converting European date to US date in Excel with formula

When Excel couldn't recognize a date (or a number actually), it assumes the cell content is a regular text. As a result, the core of the functions is to parse the date figures from the text. However, if the date strings are not formatted equally and in a proper form, you need to make your formula more dynamic.

Let's check our options from simpler to complex cases.

DD/MM/YYYY Format

If all of dates in your data has two digits month and day figures as well as you are confident about each date has reverse day and month, the formula is simple:

The formula uses the DATE function to return a date value. Because the function needs a year, month and day values as numbers, we are using text-parsing functions to retrieve the corresponding part:

• RIGHT: Retrieves 4 characters from right (year)
• MID: Retrieves 2 characters starting from 4^th (month)
• LEFT: Retrieves 2 characters from left (day)

D/M/YYYY Format

However, if the date strings are not formatted equally and in a proper form, you need to make your formula more dynamic. D/M/YYYY format is a good sample. Because if your day or month value is a single digit value, your date doesn't fit the above function.

For this type of scenarios, your formula should calculate where each figure starts and end. The best way is to locate the separator character in your date. For example, the separator is a slash character "/" in our example.

We are going to mention two separate methods based on your Excel version. Microsoft has recently released new text functions for the 365 subscription model that one of them is the TEXTSPLIT. The TEXTSPLIT function can split a text by given separator characters and return an array including split strings. Because the TEXTSPLIT and the LET function can save us redundancy, we are going to mention them in a separate topic.

Non-365 subscribers can skip the next part and continue with the latter.

Excel 365 version

As mentioned above, the TEXTSPLIT function returns an array of split values. You can return a specific value in an array by using the INDEX function.

The thing is you need to use the return array 3 times because the DATE function requires each split part as year, month and day.

Reusing a formula part over and over increases to the possibility of a mistake as well as the length of the formula. Instead, we can use the LET function to define a in-formula name for the TEXTSPLIT part and use the name.

Check how the date value is named as "MyDate" and used in TEXTSPLIT as well as how the TEXTSPLIT part becomes "YMD":

Another advantage of this structure is the maintainability. Because the actual date string is used only one time, you can easily change it at once.

Non-Excel 365 version

If you do not own Excel 365, you need to split the date string with either FIND or SEARCH function. We are using the FIND function in our example, but you can prefer the SEARCH as well.

The FIND function returns the start position of a certain string in a given text. Thus, we can use this position to calculate where text-parsing starts and how much character is needed.

See that the FIND function is used with a start number inside the MID to start searching after the 4^th character.

If you have access to the LET function (Excel 2021), the formula above can be updated to following:

Although the length of the formula is greater than the previous, it less error prone.

Wrong evaluated dates

The worst scenario is that Excel evaluates dates incorrectly if a date in the source fits in your computer's formatting. For example, "3/5/2022" is the 3^rd of May in European date format while it is the 5^th of March in US.

See how European dates are converted into US dates incorrectly after cop and pasting:

Once a date is recognized by Excel, it is converted a serial number. After that you can't apply text-parsing operation we have mentioned above.

The workaround is to change these types of dates into texts with a trick. The function we need is the TEXT function. The TEXT function allows you to convert a number to a string in a given format. This means that we can format our date serial number as a European date as it was originally.

The trick is that the format should be "MM/DD/YYYY" opposed to the European format due to Excel's incorrect evaluation.

Because Excel keeps datetime values as numbers, you can check if a date value is evaluated or not by using ISNUMBER function. The ISNUMBER function returns TRUE or FALSE according to the supplied value.

Once again based on your Excel version, you can decide between two approaches. If you have access to the LET function, you can keep the string value returning from the ISNUMBER check in memory. Otherwise, you need helper cells to return date strings first and then use the parsing formulas above.

Excel 365 or Excel 2021

The date is checked and return value is preserved in DateString name:

Non-Excel 365 version

All you need to do is to use helper cells to check if date is validated and return the string form.