How To Change Date Format In Excel From Dd/mm/yyyy To Mm/dd/yyyy

Converting the date format from dd/mm/yyyy to mm/dd/yyyy is crucial for several reasons. Firstly, it ensures compatibility with international standards, particularly in countries like the United States. Secondly, it helps avoid confusion, especially when the day and month values are both 12 or less. Additionally, converting to mm/dd/yyyy ensures consistency with data sources that primarily use this format, simplifying data integration and comparison. It is essential to review and validate the converted dates to maintain accuracy and ensure data integrity throughout the process.

The default date format in Excel is mm/dd/yyyy, but occasionally the dates in the files you import or receive are in the dd/mm/yyyy format. In this tutorial, we’ll learn how to convert these dates to the mm/dd/yyyy format in Excel. Suppose we have the below dataset, let’s see how we can change the format of the dates to our desired format.

Method 1 – Using Text Function

The TEXT function in Excel is a valuable tool for changing the date format. By using the TEXT function, you can convert a date value into a specified format of your choice. The syntax of the function is given below

=Text(Value,format_text)

Value: The numerical number that needs to be converted to text is called value.

Format_text: The formatting we wish to implement is defined as format_text.

Step – 1 – Apply Formula

Write the following formula in column C

=TEXT(A2,”MM/DD/YYYY”)

Press “Enter”.

Step – 2 – Drag the Formula.

Drag the formula downwards to apply it on all rows.

Method 2 – Using Format Cells

Step – 1 – Go to the Format Cells option

Copy the original data to another column by selecting the cells and then using CTRL+C and CTRL+V options.
Select the copied column, which needs to be modified for the date format.
Now, right-click to open up the context menu and then select the “Format Cells” option, see image.