Table of Contents
The process of converting a date format from European to U.S. in Excel involves changing the display of the date values to reflect the preferred format of the United States. This can be done by modifying the regional settings within the Excel program, which allows for the selection of different date and time formats. By adjusting the settings, the dates will appear in the desired U.S. format, making it easier to understand and work with the data. This conversion can be helpful for users who are accustomed to the U.S. date format or for those who need to present data to an American audience. With a few simple steps, the date format can be easily converted and displayed in the desired format.
Excel: Convert Date Format from European to U.S.
Europe uses a standard date format of dd/mm/yyyy while the U.S. uses a standard date format of mm/dd/yyyy.
You can easily convert a European date format to a U.S. date format in Excel by using the following formula:
=CONCAT(TEXTBEFORE(TEXTAFTER(A2, "/"), "/"), "/", TEXTBEFORE(A2, "/"), "/", TEXTAFTER(A2, "/", 2))
This particular formula converts the date in cell A2 from a European date format to a U.S. date format.
For example, this would convert a European date format of 14/5/2023 to a U.S. date format of 5/14/2023.
The following example shows how to use this formula in practice.
Example: Convert Date Format from European to U.S. in Excel
Suppose we have the following list of dates in Excel that are currently in a standard European date format:
We can type the following formula into cell B2 to convert the date format in cell A2 to a standard U.S. date format:
=CONCAT(TEXTBEFORE(TEXTAFTER(A2, "/"), "/"), "/", TEXTBEFORE(A2, "/"), "/", TEXTAFTER(A2, "/", 2))
We can then drag and fill this formula down to each remaining cell in column B:
Column B now displays each date from column A in a standard U.S. date format.
For example:
- 14/5/2023 is converted to 5/14/2023.
- 20/6/2023 is converted to 6/20/2023.
- 23/6/2023 is converted to 6/23/2023.
And so on.
How This Formula Works
Recall the formula that we used to convert the European date format in cell A2 to a U.S. date format:
=CONCAT(TEXTBEFORE(TEXTAFTER(A2, "/"), "/"), "/", TEXTBEFORE(A2, "/"), "/", TEXTAFTER(A2, "/", 2))
Here is how this formula works:
First, we use TEXTBEFORE(TEXTAFTER(A2, “/”), “/”) to extract the text between the two slashes in the date.
For the date of 14/5/2023 in cell A2, this extracts 5.
Then we use TEXTBEFORE(A2, “/”) to extract the text before the first slash. This extracts 14.
Then we use TEXTAFTER(A2, “/”, 2) to extract the text after the second slash. This extracts 2023.
Lastly, we use the CONCAT function to concatenate each of these values together with slashes to return the final result of 5/14/2023.
The formula uses this same process to convert each European date in column A into a U.S. date format in column B.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: