How to Convert Date Format dd mm yyyy to mm dd yyyy

Dates in Excel can be any format, for example, "dd mm yyyy". If you have the dates in the "dd mm yyyy" format, and you want to convert them into "mm dd yyyy" format or any other format, you need to separate the year, month and day into different parts, then combine them back with the ampersand sign or the Concat Function.

Please use the formula below to convert "dd mm yyyy" date format into "mm dd yyyy" format, and change A2 to the cell name in your document.

=TEXT(MID(A2,4,2),"00") & " " & TEXT(LEFT(A2,2),"00") & " " & RIGHT(A2,4)

For example, 06 08 2011 in Cell A2

1. TEXT(MID(A2,4,2),"00") = 08 is the month;

2. TEXT(LEFT(A2,2),"00") = 06 is the day; and

3. RIGHT(A2,4) = 2011 is the year.

Leave a Reply