Spreadsheet Practice

How to convert dd/mm/yyyy text to a date in Excel

Formula (Excel and Google Sheets)

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

Rebuild the date from its parts: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)). It works whatever your regional settings are.

Result on the example
one result per row in B2, filled down to B6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

The text is always two digits, two digits and four digits, so RIGHT takes the year, MID the month and LEFT the day, and DATE puts them in the right order. "05/03/2026" becomes March 5, 2026.

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

Result3/5/2026 (46086)

  1. A2"05/03/2026"

    The value in A2.

  2. RIGHT(A2,4)"2026"

    RIGHT: Characters from the end of text.

    • textA2"05/03/2026"
    • num_chars4
  3. MID(A2,4,2)"03"

    MID: Characters from the middle of text.

    • textA2"05/03/2026"
    • start_num4
    • num_chars2
  4. LEFT(A2,2)"05"

    LEFT: Characters from the start of text.

    • textA2"05/03/2026"
    • num_chars2
  5. DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))3/5/2026 (46086)

    DATE: Builds a date from parts.

    • yearRIGHT(A2,4)"2026"
    • monthMID(A2,4,2)"03"
    • dayLEFT(A2,2)"05"

In Google Sheets

The same formula works in Google Sheets: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=DATEVALUE(MID(A2,4,3)&LEFT(A2,3)&RIGHT(A2,4))3/5/2026, 2/28/2026, 12/1/2026…Swaps day and month into US order and lets DATEVALUE read it. Only works with US date settings.
=DATE(TEXTAFTER(A2,"/",2),TEXTBEFORE(TEXTAFTER(A2,"/"),"/"),TEXTBEFORE(A2,"/"))Excel only3/5/2026, 2/28/2026, 12/1/2026…Splits on the slashes, so it also handles 5/3/2026 without leading zeros. Excel 365.

Common mistakes

  • DATEVALUE with US settings reads "05/03/2026" as May 3, a silent mistake, and returns #VALUE! for "28/02/2026".
  • Fixed positions break on "5/3/2026". Use the TEXTBEFORE and TEXTAFTER version, or Data > Text to Columns with the DMY option.
  • If Excel already converted some values on import, "05/03/2026" may now be a real date for May 3. Those are numbers, not text; check with ISTEXT before applying the formula.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Convert dd/mm/yyyy text to a date

In B2, convert the day-first text date in A2 to a real date, and fill down to B6.

Questions

How do I show the result as dd/mm/yyyy?

Give the converted dates the custom number format dd/mm/yyyy, or use =TEXT(B2,"dd/mm/yyyy") for text.

How do I fix this in Google Sheets?

The same DATE formula works. Or set File > Settings > Locale to a day-first country before importing.

What about dates with dots, like 14.03.2026?

The positions are the same, so the same formula works.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.