Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create Area Chart in Excel
  • How to create Checklist in Excel
  • Excel Bar Chart
  • How to sum a total in multiple Excel tables
  • Conditional Formatting New Rule with Formulas in Excel

References

  • How to get last row in text data in Excel
  • Find closest match in Excel
  • Last row number in range
  • How to get address of first cell in range in Excel
  • Basic INDEX MATCH approximate in Excel

Data Validations

  • Excel Data validation must not contain
  • Excel Data validation only dates between
  • Excel Data validation must begin with
  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only

Convert text date dd/mm/yy to mm/dd/yy in Excel

by

This tutorial shows how to Convert text date dd/mm/yy to mm/dd/yy in Excel using example below.

To convert dates in text format dd/mm/yy to a true date in mm/dd/yy format, you can use uses a formula based on the DATE function.

Formula

=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))

Explanation

In the example shown, the formula in C5 is:

=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2))

Which converts the text value in B5 “29/02/16” into a proper Excel date.

How this formula works

The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:

The year value is extracted with with the RIGHT function:

RIGHT(B5,2)+2000

RIGHT gets the right-most 2 characters from the original value. The number 2000 added to the result to create a valid year. This number goes into DATE as the year argument.

The month value is extracted with:

MID(B5,4,2)

MID retrieves characters 4-5. The result goes into DATE as the month argument.

The day value is extracted with:

LEFT(B5,2)

LEFT grabs the final 2 characters of the original text value, which goes into DATE as the day argument.

The three values extracted above go into DATE like this:

=DATE(2016,"02","29")

Although month and day are supplied as text, the DATE function automatically converts to numbers and returns a valid date.

Note: the year value 2016 was automatically converted to a number when 2000 was added.

Dealing with extra space

If the original text value contains extra leading or trailing space characters, you can add the TRIM function to remove:

=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • How to use Excel NOT Function
  • How to use Excel AND Function
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • Add workdays to date custom weekends in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Convert decimal minutes to Excel time
  • Get days between dates ignoring years in Excel
  • Add workdays no weekends in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Group times into 3 hour buckets in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel

General

  • Find, Select, Replace and Go To Special in Excel
  • How to get Excel workbook path only
  • How to calculate percent of students absent in Excel
  • How to Create Calendar in Excel
  • Zoom Worksheet in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning