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

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

**Excel:** `=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date (dd/mm/yyyy) | Date |
| 05/03/2026 |  |
| 28/02/2026 |  |
| 01/12/2026 |  |
| 17/07/2026 |  |
| 09/10/2026 |  |

Formula in B2, filled to B6: `=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))`

Result: 3/5/2026; 2/28/2026; 12/1/2026; 7/17/2026; 10/9/2026

## 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.

## Other ways

- `=DATEVALUE(MID(A2,4,3)&LEFT(A2,3)&RIGHT(A2,4))`: 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,"/"))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-dd-mm-yyyy-text-to-a-date