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

Data Analysis

  • Working With Tables in Excel
  • How to Create Area Chart in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Get column index in Excel Table
  • Remove Duplicates Example in Excel

References

  • Extract data with helper column in Excel
  • How to use Excel INDIRECT Function
  • How to retrieve first match between two ranges in Excel
  • How to use Excel LOOKUP Function
  • How to reference named range different sheet in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation don’t exceed total
  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation must contain specific text

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:

Worked Example:   Extract word that begins with specific character in Excel

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.

Worked Example:   How to get first word in Excel

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.

Worked Example:   How to count total words in a range in Excel

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:

Custom weekday abbreviation in Excel

Next Post:

Convert Excel time to Unix time in Excel

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

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • How to calculate Next working/business day in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Calculate retirement date in Excel
  • Add decimal minutes to time in Excel
  • Extract time from a date and time in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel

General

  • How to get original number from percent change in Excel
  • Index and match on multiple columns in Excel
  • How to set or clear a print area in Excel Worksheet
  • How to get amount with percentage in Excel
  • Subtotal invoices by age in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning