When 05.01.2024 or 05 01 2024 stays as text, Excel is not recognising the separator. Either replace the separator with a slash, rebuild the date from its parts with a formula, or split it with Text to Columns and rebuild it with DATE.
Why are these not recognised?
Excel accepts a short list of separators for dates, mainly slash and hyphen in India-style settings. Dots, spaces and some symbols are not on the list in every region. Statement conversions also bring non-breaking spaces that look like ordinary spaces. The result is a column that sits left-aligned and will not sort or group by month.
Method 1: Find and Replace
- Select the date column only. If you press Replace across the sheet you will turn decimal points in the amount columns into slashes.
- Press Ctrl+H. In Find what, type a full stop. In Replace with, type a slash.
- Press Replace All.
Excel usually re-reads each changed cell, and a cell that now looks like 05/01/2024 may become a date right away. That re-reading follows your regional order, so on a US-style setting you can get a swapped date or a text cell. Check a row with a day above 12 straight after.
Method 2: a formula that ignores the separator
If every date is written with two-digit day and month and a four-digit year, the positions never change, so the separator is irrelevant:
- =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))
This reads 05.01.2024, 05-01-2024 and 05 01 2024 alike, and returns 5 January 2024 every time. A non-breaking space at the start or end of the cell would shift those positions, so clean the cell first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) and point the formula at the cleaned text.
Method 3: Text to Columns, then DATE
- Select the column, then Data, Text to Columns, Delimited, Next.
- Tick Other and type the separator. You can type more than one character, such as a dot and a hyphen. Tick Space if the gaps are spaces. Press Next.
- Set a destination in empty columns, say $G$2, so the original stays. Press Finish. You now have day, month and year in three columns.
- In J2, rebuild the date: =DATE(I2,H2,G2). The order is year, month, day, so the last column comes first.
Because Text to Columns hands you plain numbers, 5.1.2024 and 15.11.2024 split correctly even though the widths differ. Say the three columns hold 5, 1 and 2024. DATE(2024,1,5) is 5 January 2024.
The three methods suit different situations:
| Method | Best when | Watch out for |
|---|---|---|
| Find and Replace | One consistent separator, quick job | Region-order swaps, and decimals in other columns |
| Formula by position | Always two-digit day and month | Hidden spaces shifting the positions |
| Text to Columns, then DATE | Mixed widths or separators | Needs spare columns, and a one-off run |
If you will repeat the job monthly, the formula version is the easiest to keep, because it recalculates when you paste new text into column A.
How do I check that it worked?
- =ISNUMBER(J2) should be TRUE for every row. Fill it down, or use =COUNT(J2:J500) against your row count.
- Dates should be right-aligned and sort in calendar order.
- A date with the day above 12 should show the right month. For example 18.02.2024 should read 18 February.
Keep an eye on years written with two digits, such as 05.01.24. The position formula above expects four, so use RIGHT(A2,2) with 2000 added, or you will build a date in 1924.
Tidy up
- Format the result as dd/mm/yyyy.
- Copy the date column and paste as values over itself, so deleting the helper columns does not break it.
- Keep the original text column in a hidden column or a copy sheet until you have compared the first and last date against the statement.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


