Dates that come in as text or have day and month swapped

You import an export from your bank, online shop or accounting system and the dates look odd: 03/04/2027 turns into 4 March instead of 3 April, or you can't calculate or sort with them. In that case, the dates are stored as text.

How to recognise it

• Text dates are aligned to the left of the cell, real dates to the right.
• If you choose Long date as the number format and nothing changes, it's text.
• A formula such as =C3+30 returns #VALUE! or something strange.

First, set the region correctly

Go to File, Advanced settings and set Region to Dutch (Netherlands) (see Taking over formulas from Excel: English function names and the comma). This makes Euro-Office read everything you type yourself as day-month-year.

Converting text into a real date

If A3 contains the text 03/04/2027 (day/month/year), enter this in C3:

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

RIGHT takes the last four characters (the year), MID takes the two characters starting at position 4 (the month) and LEFT takes the first two (the day). DATE turns this into a real date.

1. Drag the formula down.
2. Apply the Short date format to column C.
3. Want to get rid of the old column? Copy column C, right-click and choose Paste special with values only. Then you can delete column A.

American dates (month/day/year)

If the export comes from an American system (04/03/2027 for 3 April), swap MID and LEFT around: =DATE(RIGHT(A3,4),LEFT(A3,2),MID(A3,4,2)).

Pitfalls

• Are the day or month sometimes a single digit (3/4/2027)? Then the fixed position won't be correct. In that case, first use Text to Columns on the Data tab with the forward slash as the separator (under Other), then apply =DATE(year,month,day) to the three columns.
• Don't use =TEXT() to "fix" this: it just produces text again.
• If the data comes from a CSV file, also check Opening CSV files with semicolons and strange characters correctly.