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*.

## 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.

If you are just exchanging Excel files (xlsx, xlsm, etc.), you are good due to Excel's date-storing approach.

## 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:

=DATE(VALUE(RIGHT(C7,4)),VALUE(MID(C7,4,2)),VALUE(LEFT(C7,2)))

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.

=LEFT("3/5/2025",2) *returns "2/" which is obviously not a valid number for day*.

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.

**Reminder:** Microsoft may have already been included the **TEXTSPLIT **function into retail package while you are reading this article. So, please check if the function is available or not in your Excel to be sure.

#### 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.

*returns {"17","5","2007"}*=INDEX(TEXTSPLIT("17/5/2007","/"),2)

*returns "5", the 2*

^{nd}value in the arrayThe 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":

=LET(MyDate,"3/5/2012",YMD,TEXTSPLIT(MyDate,"/"),DATE(INDEX(YMD,3),INDEX(YMD,2),INDEX(YMD,1)))

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.

=DATE(

RIGHT(C25,4),

MID(C25,FIND("/",C25)+1,FIND("/",C25,4)-FIND("/",C25)-1),

LEFT(C25,FIND("/",C25)-1))

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:

=LET(

MyDate,C33,

DaySep,FIND("/",MyDate),

DATE(

RIGHT(MyDate,4),

MID(MyDate,DaySep+1,FIND("/",MyDate,4)-DaySep-1),

LEFT(MyDate,DaySep-1)))

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.

=TEXT("3/5/2012","MM/DD/YYYY")* returns "03/05/2012". Do NOT use "DD/MM/YYYY"*

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.

*returns TRUE*ISNUMBER("6/5/2006")

*returns FALSE*

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:

MyDate,"27/5/2010",

DateString,IF(ISNUMBER(MyDate),TEXT(MyDate,"MM/DD/YYYY"),MyDate),

YMD,TEXTSPLIT(DateString,"/"),

DATE(

INDEX(YMD,3),

INDEX(YMD,2),INDEX(YMD,1)))

#### 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.

*returns "12/10/2011"*IF(ISNUMBER("27/5/2010"),TEXT("27/5/2010","MM/DD/YYYY"),"27/5/2010") returns

*"27/5/2010"*