Spreadsheets

Formula to turn dd-mm-yyyy text into a date with DATE, LEFT and MID

Excel formula to turn dd-mm-yyyy text into a date: build it with DATE, LEFT and MID, learn why DATEVALUE depends on region, and cover dates without zeros.

Editorial illustration for Formula to turn dd-mm-yyyy text into a date with DATE, LEFT and MID

For fixed-width text such as 05-01-2024, use =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)). It takes the year from the last four characters, the month from characters four and five, and the day from the first two, so it works whatever your regional settings are.

Reading the formula piece by piece

PartWhat it returns for 05-01-2024Why
LEFT(A2,2)05, the dayThe first two characters
MID(A2,4,2)01, the monthStart at character 4, take two
RIGHT(A2,4)2024, the yearThe last four characters
DATE(year, month, day)5 January 2024Builds a true date

Note the argument order. DATE wants year, month, day, which is the reverse of how the text is written, so the formula starts with RIGHT and ends with LEFT. Format the result cell as dd/mm/yyyy, because DATE returns a serial number (45296 for this one) until formatted.

Why not just use DATEVALUE?

DATEVALUE("05-01-2024") reads the text using your computer's date order, so the same formula gives 5 January on one machine and 1 May on another. It is not wrong, it is region-dependent, which is exactly the trap you are trying to leave behind. DATEVALUE is safe when the text has a month name, such as 05-Jan-2024, or when it is written year first, as in 2024-01-05, but even then it is worth testing on your own machine.

A positional DATE formula does the same job on every computer, because it never asks Excel to guess the order.

What if the days and months have no leading zeros?

Text such as 5-1-2024 has a variable width, so fixed positions fail. Find the two dashes instead. In B2 and C2 locate them, then build the date in D2:

  • B2: =FIND("-",A2)
  • C2: =FIND("-",A2,B2+1)
  • D2: =DATE(MID(A2,C2+1,4),MID(A2,B2+1,C2-B2-1),LEFT(A2,B2-1))

B2 is the position of the first dash, C2 the second. The day is everything before the first dash, the month sits between the dashes, and the year follows the second. This version also copes with 15-11-2024 and 5-1-2024 in the same column.

A few variants of the same idea cover most statements:

  • Blank-safe: =IF(A2="","",DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))) leaves empty rows empty instead of showing an error or a date in 1900.
  • Slashes instead of dashes: the positions are identical, so the same formula works for 05/01/2024.
  • Year first, as in 2024-01-05: =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)).

Take 15-11-2024 as a check. LEFT gives 15, MID gives 11 and RIGHT gives 2024, so DATE(2024,11,15) is 15 November 2024. A day above 12 proves the order: it could never be a month.

Troubleshooting the result

  • #VALUE! usually means a hidden space or a non-breaking space. Wrap the text: TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
  • A date in the year 1900 or 1905 means a part was read as the wrong number. Print each piece in its own cell to see which.
  • A column of ##### only means the column is too narrow for the date.
  • Two-digit years need 2000+ added to the year piece, or the date may land in 1924. A separate article covers the century rule.

Fill the formula down the whole column before judging it. One odd row, perhaps a footer line or a date typed with a different separator, shows up as an error, and a clean column of results is much more convincing than one lucky cell.

Check before you trust it

  1. Copy the results and paste as values over themselves if you need to delete the text column later.
  2. Use =COUNT(D2:D500) to count real dates, and compare with the number of rows.
  3. Check the smallest and largest with =MIN(D2:D500) and =MAX(D2:D500). They should match the first and last dates of the statement.

If your sheet is stuck at this stage every month, that is the point where an import tool saves time. SpendixAI reads statements you upload and lets you review the rows before they are saved, though a formula like this one is still the better answer when you are staying inside Excel.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports