This post will guide you how to convert non-standard date formats or text to a standard date in Excel. If you have a date with mmddyy format, and you want to change it as the normal date format as mm/dd/yyyy with Excel Formula. How do I convert the mmddyyyy text to normal date format in Excel 2013/2014.

Assuming that you have a list of data in range B1:B4 which contain date values as text format. and you can use one of the following methods to convert it as normal date format.

1. Convert mmddyyyy Text to Date with Formula

To convert the mmddyyyy text to normal date format, you can use a formula based on the DATE function, the MID function and the RIGHT function. Just like this:

=DATE(RIGHT(B1,4),LEFT(B1,2),MID(B1,3,2))

Type this formula into a blank cell and press Enter key, and then drag the AutoFill Handle down to other cells to apply this formula to convert it.

2. Convert mmddyy Text to Date with Text To Column Feature

You can also convert mmddyyyy Text to a normal date format with Text to Column feature in Excel. Just do the following steps:

Step1:select the range of cells that you want to convert, such as: B1:B4.

Step2: go to Data tab in the Excel Ribbon, and click Text to Column command under Data Tools group. And the Convert Text to Columns dialog box will appear.

Step3: select Delimited option under Original Data type section in the Convert Text to Columns dialog box, and click Next button. Click Next button again.

Step4: select Date option under the Column Data format, and then choose MDY from the drop down list box of Date. Then select on destination cell to place data. Click Finish button.

Step5: you would see that the selected cells have been converted to a normal date format.

3. Convert mmddyy Text to Date with VBA Macro

If you want to convert mmddyyyy text to a normal date format quickly, you can also use an Excel VBA Macro to achieve the same result. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

el v

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub ConvertDate_mmddyy_Format()
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim sourceCell As Range
    Dim destinationCell As Range
    
    ' Prompt to select source range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the range containing the mmddyy format:", Type:=8)
    On Error GoTo 0
    
    ' Check if a range is selected
    If sourceRange Is Nothing Then
        MsgBox "No source range selected. Operation canceled.", vbExclamation
        Exit Sub
    End If
    
    ' Prompt to select destination range (must be the same size as source range)
    On Error Resume Next
    Set destinationRange = Application.InputBox("Select the destination range (same size as source range):", Type:=8)
    On Error GoTo 0
    
    ' Check if a range is selected and if it's the same size as the source range
    If destinationRange Is Nothing Or destinationRange.Rows.Count <> sourceRange.Rows.Count Or destinationRange.Columns.Count <> sourceRange.Columns.Count Then
        MsgBox "No valid destination range selected. Operation canceled.", vbExclamation
        Exit Sub
    End If
    
    ' Convert each cell in the source range
    For Each sourceCell In sourceRange
        ' Get corresponding destination cell
        Set destinationCell = destinationRange.Cells(sourceCell.Row - sourceRange.Row + 1, sourceCell.Column - sourceRange.Column + 1)
        ' Convert mmddyy format to Date
        destinationCell.Value = DateSerial(Right(sourceCell.Value, 2), Left(sourceCell.Value, 2), Mid(sourceCell.Value, 3, 2))
    Next sourceCell
    
    MsgBox "Conversion completed successfully!", vbInformation
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Follow the prompts to select the source cell containing the mmddyy format and then select the destination cell to place the result.

Once done, the macro will convert the mmddyy format to Date and display a message indicating the completion of the operation.

4. Video: Convert mmddyy Text to Date

This Excel video tutorial, we’ll explore three methods to convert mmddyy format to Date. We’ll start with a formula approach using the DATE function, then utilize the Text To Column feature, and finally, employ VBA code.

5. Related Functions

• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
• Excel RIGHT function
  The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
• Excel DATE function
  The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…