To convert a column of dd/mm/yyyy text to real dates, select it, open Data, Text to Columns, click Next twice, set the column format to Date and choose DMY, then Finish. Afterwards the entries are right-aligned numbers you can sort and sum by month.
How do I know the dates are text?
Text dates usually sit against the left edge of the cell, show a small green triangle, or refuse to sort properly. Confirm with a formula in a spare cell: =ISNUMBER(A2) returns FALSE for text and TRUE for a real date. A real date is a serial number underneath. In a hypothetical case, 05/01/2024 is the number 45296, which shows up if you switch the format to General.
The Text to Columns steps
- Select only the date column, for example A2:A500. Text to Columns works on one column at a time.
- Choose Data, then Text to Columns.
- Step one: pick Delimited and press Next.
- Step two: untick every delimiter. You are not splitting anything, and leaving Tab or Space ticked can cut the cell. Press Next.
- Step three: under Column data format, pick Date and choose DMY from the box beside it.
- Check the Destination box. By default it overwrites the original column, which is fine if you copied the sheet first. To keep the originals, enter another cell such as $G$2.
- Press Finish.
Excel parses every cell as day, month, year no matter what your Windows region says, which is exactly why this trick beats just formatting the cells.
What do I do afterwards?
- Right-click the column, choose Format Cells, and pick Custom with dd/mm/yyyy so every row looks the same.
- Check the numbers: =COUNT(A2:A500) should equal the number of transactions, and =ISNUMBER(A2) should be TRUE.
- Test a row where the day is above 12, such as 18/02/2024, and one where it is not, such as 05/01/2024. Both should read as 18 February and 5 January.
| Text in the cell | Read as DMY | Serial number |
|---|---|---|
| 05/01/2024 | 5 January 2024 | 45296 |
| 18/02/2024 | 18 February 2024 | 45340 |
| 13/02/2024 | 13 February 2024 | 45335 |
Those serial numbers are a useful sanity check: for 2024, 1 January is 45292, so a date in early January should be a small step beyond that. If a January date lands near 45400, it was read as another month.
Why did nothing change?
The usual reasons the trick fails, and what to do about each:
- Hidden spaces. Dates copied from a PDF often carry a trailing space or a non-breaking space. In a helper column use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) and run Text to Columns on the cleaned values.
- A leading apostrophe. A cell that starts with an apostrophe in the formula bar is stored as text. Text to Columns usually converts it, but test on a few cells first.
- The column format is Text. Switch it to General before step three; the wizard then converts as it should.
- Two-digit years or month names. Those usually work, but look at the results with a century check, since yy may land in the wrong century.
One more trap: if a few rows refuse to convert, they usually hold a day that does not exist in that month, such as 31/04/2024, or a typo in the separator. Filter the column for text with =ISTEXT(A2) in a helper column and read the survivors one by one.
A quick closing check
- Sort the column oldest to newest and confirm the first and last dates match the statement period.
- Filter by month and confirm nothing from the wrong year appears.
- Save a copy before the next paste.
If all of this feels like a lot for one column, remember that tools that import statements directly, SpendixAI among them, read the dates for you and show the rows for review first. Use whichever route you will actually keep doing.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


