4 Ways To Convert Ddmyyy To Mmddyy In Excel

The world of date formatting in Excel can be a bit tricky, especially when dealing with different date formats. One common challenge is converting dates from the DDMMYY format to the MMDDYY format. In this article, we will explore four ways to achieve this conversion in Excel.

Understanding the Date Formats

Before we dive into the conversion methods, let's understand the two date formats:

DDMMYY: This format represents the date as day-month-year (e.g., 12-03-22).
MMDDYY: This format represents the date as month-day-year (e.g., 03-12-22).

Method 1: Using the TEXT Function

Using the TEXT function for date conversion

The TEXT function in Excel can be used to convert the date format from DDMMYY to MMDDYY. Here's the formula:

=TEXT(A1,"MM/DD/YY")

Assuming the date in the DDMMYY format is in cell A1, this formula will convert it to the MMDDYY format.

How it works:

The TEXT function takes two arguments: the value to be formatted (A1) and the format code ("MM/DD/YY").
The format code "MM/DD/YY" tells Excel to display the date in the MMDDYY format.

Method 2: Using the DATE Function

Using the DATE function for date conversion

Another way to convert the date format is by using the DATE function in combination with the RIGHT, LEFT, and MID functions. Here's the formula:

=DATE(RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

Assuming the date in the DDMMYY format is in cell A1, this formula will convert it to the MMDDYY format.

How it works:

The DATE function takes three arguments: year, month, and day.
The RIGHT function extracts the last two characters (year) from cell A1.
The MID function extracts the middle two characters (month) from cell A1.
The LEFT function extracts the first two characters (day) from cell A1.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows you to manipulate and transform data. Here's how to use Power Query to convert the date format:

Go to the "Data" tab in the ribbon and click on "From Other Sources" > "From Microsoft Query".
Select the table or range containing the dates in the DDMMYY format.
In the Power Query Editor, click on "Add Column" > "Custom Column".
Enter the formula: =Date.FromText([Date Column])
Click "OK".
Go to the "Home" tab in the Power Query Editor and click on "Close & Load".

How it works:

The Date.FromText function converts the text date to a date value.
The resulting date value is then loaded into a new column in the worksheet.

Method 4: Using VBA Macro

If you prefer to use VBA macros, here's a code snippet that converts the date format from DDMMYY to MMDDYY:

Sub ConvertDateFormat()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = Format(cell.Value, "MM/DD/YY")
    Next cell
End Sub

How it works:

The VBA macro loops through each cell in the selected range.
The Format function converts the date value in each cell to the MMDDYY format.

Gallery of Date Conversion Examples

DDMMYY to MMDDYY date conversion examples

We hope this article has helped you learn four ways to convert dates from the DDMMYY format to the MMDDYY format in Excel. Whether you prefer using formulas, Power Query, or VBA macros, there's a method that suits your needs. If you have any questions or need further assistance, feel free to comment below!