Converted dates go wrong in Excel for four reasons: the day and month were swapped, the date arrived as text, a two-digit year landed in the wrong century, or the statement never printed a year. Each fault has its own repair, usually one formula or a Text to Columns pass, followed by a quick check against the PDF.
Why are converted dates wrong in Excel?
A converted date is wrong when Excel reads the printed characters with a different rule than the bank used to write them. The PDF only holds text such as 03/04/26. Excel has to decide which part is the day, which is the month and which century the year belongs to, and it decides using your computer's settings.
That gives four separate faults, and a single statement can show more than one:
- Order fault. The bank wrote day first and Excel read month first, or the reverse.
- Type fault. Excel could not read the text as a date at all, so the cell holds text that looks like a date.
- Century fault. A two-digit year was given the wrong century.
- Missing year. The statement printed only a day and a month, and Excel filled in a year of its own choosing.
A worked example of six wrong dates
The table below uses example rows from a made-up statement written day first, opened on a computer set to month first. The fix formulas assume the bad value is in cell A2.
| Printed on the PDF | What Excel shows | Fault | Fix |
|---|---|---|---|
| 03/04/2026 | 4 March 2026, a real date | Order | =DATE(YEAR(A2),DAY(A2),MONTH(A2)) |
| 14/04/2026 | 14/04/2026, left aligned | Type | =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) |
| Maturity 01/06/45 | 1945, not 2045 | Century | =DATE(YEAR(A2)+100,MONTH(A2),DAY(A2)) |
| 2 Jan | 2 January of the current year | Missing year | Year taken from the statement period |
| 31 Dec | 31 December of the current year | Missing year across January 1 | Year from the period start |
| 2026-04-15 | 15 April 2026 | None | Leave it alone |
The pattern is worth noticing. The rows that went wrong silently are the dangerous ones, because a date of 4 March looks perfectly normal. The rows that stayed text are easier to spot, since they sit at the left of the cell and refuse to sort.
Swapped day and month in converted statement dates
Day and month swap when a statement written as day, month, year is read on a computer that expects month, day, year, or the other way round. Any date whose day is 12 or lower becomes a real but wrong date. Dates with a day of 13 or higher cannot be a month, so they stay as text.
That split is the best clue you have. If a date column shows some cells right aligned and some left aligned, and the left aligned ones all have a first number above 12, the column was read in the wrong order. Check one row against the PDF to confirm which order the bank used.
Repairing a mixed column in one formula
A mixed column holds two kinds of cells: wrong real dates and text. One formula in a helper column can handle both, assuming the source was written as dd/mm/yyyy:
=IF(ISNUMBER(A2), DATE(YEAR(A2),DAY(A2),MONTH(A2)), DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))
The first half swaps the day and month back on the real dates. The second half reads the text by position. Fill the formula down, check a few rows, then copy the helper column and paste it over the original with Paste Special, Values.
Re-reading the column with Text to Columns
If the cells still hold the original text, Text to Columns is cleaner than a formula. Select the column, choose Data, then Text to Columns, pick Delimited, clear every delimiter, and in the last step choose Date with the order the bank used, such as DMY. Excel then reads every cell with that order instead of your computer's default.
Converted dates stored as text instead of date values
A text date is a cell that shows a date but holds plain characters, so Excel cannot sort it, filter it by month or subtract it from another date. Turn it into a real date with Text to Columns and the right date order, or with a DATE formula that picks the year, month and day out of the text.
You can test any cell with =ISNUMBER(A2). A real date returns TRUE, because Excel stores dates as serial numbers and only displays them as dates. Text returns FALSE, whatever it looks like.
Text dates with month names
Some statements print dates as 14 Apr 2026 or Apr 14. Excel often reads these on its own, but a stray space or a non-breaking space from the PDF can stop it. Try Find and Replace on the column, replacing a double space with a single space, then run Text to Columns as above. If a month name is in another language, Excel on an English system will not recognise it, and a lookup table that maps each month name to its number is the reliable route.
Hidden characters around the date
Converted text sometimes carries a leading space or an invisible character before the date. =TRIM(A2) removes ordinary extra spaces. If =LEN(A2) still reports more characters than you can see, the cell holds a character that TRIM does not touch, and hidden spaces and non-breaking characters covers how to strip it.
How does Excel choose the century for a two-digit year?
Excel uses a cutoff to give a two-digit year its century. By default, years 00 to 29 become 2000 to 2029 and years 30 to 99 become 1930 to 1999. Microsoft's article on how Excel works with two-digit year numbers also notes that this rule is always in effect for imported dates.
That last point matters for converted files. Windows lets you change the cutoff year in its regional settings, but the same Microsoft article says the setting only changes dates typed into a cell. A date that arrives through an import is read with the fixed 2029 rule. So changing your settings will not fix a conversion after the fact.
When the century fault shows up
For transaction dates on a current statement, a two-digit year such as 26 lands in 2026 and causes no trouble. The fault appears on dates far in the future or the past. A loan or deposit statement may print a maturity date of 01/06/45, meaning 2045, and Excel stores it as 1945, as in the third row of the example table above. An archived statement from before 1930 has the opposite problem, though few people convert one.
Repairing a wrong century
Spot the rows by filtering the date column for any year before your statement period. If the day and month are right and only the century is wrong, add 100 years with =DATE(YEAR(A2)+100,MONTH(A2),DAY(A2)). If a maturity date is meant to be in the past, as on a closed account, leave it as it is. The cutoff is a guess, so the PDF decides.
Statement dates printed without a year
Many statements print transaction dates as day and month only, because the year sits in the statement period at the top of the page. When Excel reads a two-part date like 2 Jan, it assumes the current year, as Microsoft's article on two-digit years describes for day and month entries. That year is often wrong.
The fault is easy to miss on a statement from this year, because the guess happens to be right. It shows up on older statements, and on any statement whose period crosses January 1. A December to January statement converted today puts the December rows in the wrong year, and the running order of the sheet then jumps backwards.
Adding the year from the statement period
Put the statement start date in a cell, for example F1, and the end date in F2. Then rebuild each date in a helper column:
=DATE(IF(MONTH(A2)>=MONTH($F$1), YEAR($F$1), YEAR($F$1)+1), MONTH(A2), DAY(A2))
The formula gives each row the start year unless its month is earlier than the start month, which can only happen after the period has crossed into the new year. It works for any period shorter than twelve months.
Worked example across the new year
Take an example statement period of 15 December 2025 to 14 January 2026, with F1 holding 15 December 2025. A row printed 31 Dec has month 12, which is not earlier than the start month, so it gets 2025. A row printed 2 Jan has month 1, which is earlier, so it gets 2026. Both now sort in the order the bank posted them.
Changing the date format does not repair a wrong date
Changing the cell format only changes how a date is displayed. It never changes the stored value. If Excel stored 4 March when the bank meant 3 April, switching the format to dd/mm/yyyy shows 04/03/2026, which is still 4 March. The fix has to change the value, with a formula or by reading the text again.
This is the most common wrong turn with converted dates. The display changes, the cell looks closer to the PDF, and the error moves from obvious to hidden. Sorting and monthly totals will still use the wrong dates.
Format text dates with any date format and nothing happens at all, because formats only act on numbers. The same rule applies to amounts, and rebuilding number formats after a PDF conversion explains the two moves: make the value real, then format it.
Once the values are right, a display format is still worth choosing. A format such as yyyy-mm-dd reads the same in every country, so anyone you send the file to sees the date you meant.
A date check before trusting a converted statement
A date check compares the converted dates with the statement itself, so you know every row is right before you sort, total or import. Five checks take a few minutes on a typical statement and catch all four faults.
- Type. Next to the date column, enter
=ISNUMBER(A2)and fill down. Every row should be TRUE. - Range. Find the earliest and latest dates with
=MIN(A:A)and=MAX(A:A). Both should fall inside the statement period. - Order. Statements usually list transactions in date order. A date that is earlier than the row above it, outside a page break, is worth a second look.
- Spot check. Compare three rows against the PDF: the first, the last, and one with a day of 12 or lower.
- Count by month. On a statement that crosses January 1, count the rows per month and compare with the PDF.
Problems the check uncovers elsewhere
Dates that look fine but leave gaps in the type column often belong to rows that are not transactions at all, such as a page header or an empty line. Removing blank rows after a PDF conversion clears those first. A date that appears only once above a group of rows usually came from a merged cell, and unmerging cells after a PDF conversion shows how to spread it to every row.
If the statement started as a photo or a scan, some date errors are reading errors rather than order errors: an 8 read as a 3, or a 1 read as a 7. The check above still catches them, but the cause sits upstream, and turning a photo or screenshot into rows covers how to get a cleaner read.
FastBankConvert is being built to put each transaction's date in its own column next to the description, amount and balance. It is not open yet, and you can ask to hear when trial conversions start at Get started.