Bank statement date formats break in Excel when the bank's order of day, month and year differs from the one Excel expects, or when the date arrives as text. Fix them by importing with the right date order or locale, or by rebuilding each date with DATEVALUE or DATE, then checking a row dated after the 12th.
Why bank statement dates arrive in Excel as text
Bank statement dates arrive in Excel as text when Excel cannot read the string as a date in the order it expects. A bank in one country writes 14/03/2026, a computer set to month-first order sees month 14, and the cell stays text. Short dates without a year, compact dates like 20260314 and copied PDF text cause the same result.
Excel decides at the moment a file is opened. Microsoft's article on importing or exporting text files says Excel opens a .csv file with "the current default data format settings," and warns that a CSV column may be in month-day-year order while Excel's default is different. The DATEVALUE function page adds that results depend on the computer's system date settings.
Power Query, the tool behind Data, From Text/CSV, works the same way with its own locale. The Power Query data types documentation gives the example of a United States locale meeting a CSV in day/month/year order: setting the column to Date produces errors, "Because there's no month 22 in the calendar."
A text date looks right but behaves wrong. It will not sort in date order, it cannot be grouped by month, and a formula that subtracts one date from another returns an error. Test a column by sorting it: if 02/01 sorts before 11/30 regardless of year, the column is text or the wrong order.
The fastest cure is to avoid the problem at import. The steps for setting column types during import are in the post on importing bank statements into Excel. When the text dates are already in the sheet, the next section fixes them.
How do you convert bank statement text dates to real dates?
Convert bank statement text dates to real dates with DATEVALUE when the text is in an order Excel already recognizes, and with DATE plus LEFT, MID and RIGHT when it is not. Both return a date serial number. Format the result as a date, check a few rows against the statement, then paste the results back as values.
Microsoft's page on converting dates stored as text sets out the DATEVALUE method: enter the formula in a blank cell, fill it down, copy the results, paste them as values over the text dates, then apply a date format. The same page says Excel's error checking can flag text dates with two-digit years and offer "Convert XX to 20XX."
DATE rebuilds a date from its parts. The DATE function page gives the syntax DATE(year,month,day) and shows =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)) turning 20081125-style text into a date. The same pattern works for any fixed layout once you know where each part sits.
Before and after: five bank statement date strings
The formulas assume the text is in cell A2 and that days and months always have two digits. The result is what the cell shows after a date format is applied. All values are examples.
| Text from the statement | Formula | Real date |
|---|---|---|
| 03/14/2026 (month first) | =DATEVALUE(A2) on a month-first system | 14 Mar 2026 |
| 14/03/2026 (day first) | =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) | 14 Mar 2026 |
| 20260314 | =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) | 14 Mar 2026 |
| 03/14/26 | =DATE(2000+RIGHT(A2,2),LEFT(A2,2),MID(A2,4,2)) | 14 Mar 2026 |
| 12/29 (no year, statement closing January 2027) | =DATE(IF(VALUE(LEFT(A2,2))>$B$2,$B$1-1,$B$1),LEFT(A2,2),RIGHT(A2,2)) with the closing year in B1 and closing month in B2 | 29 Dec 2026 |
Two notes on the table. The DATE page says years from 0 to 1899 are added to 1900, so DATE(26,3,14) means 1926; adding 2000 to a two-digit year avoids that. And DATEVALUE only suits the first row on a computer set to month-first dates, because it follows the system settings.
For a column of single-digit days such as 3/4/2026, the positions in LEFT and MID move, and these formulas break. Import the file again with the right date order rather than writing a formula for every length.
Bank statement dates without a year
Bank statement dates without a year, such as "03/14," are common on statements and in copied PDF text, because the period at the top of the page already gives the year. Excel has no period to read. The fix is to supply the year from the statement itself, and to watch statements that cross from December into January.
DATEVALUE guesses the year for you, which is the problem. The DATEVALUE page says that if the year is omitted, it uses the current year from the computer's clock. A 2026 statement converted in 2027 would come out with 2027 dates, and the error is easy to miss because the day and month look right.
Put the statement year in a cell instead and build the date with DATE, as in the fifth row of the table above. For a statement whose period sits inside one year, a single year cell is enough: =DATE($B$1,LEFT(A2,2),RIGHT(A2,2)).
Statements that cross the year need one more rule. A card cycle from December 18 to January 17 has rows from both years. Any row whose month is later than the closing month belongs to the year before the closing year. That is what the IF in the table does: 12/29 on a statement closing in January 2027 becomes 29 December 2026, while 01/05 on the same statement stays in 2027.
Check the boundary rows by eye. Sort by the new date column and confirm the first and last rows match the statement period printed at the top of the page.
Which date should a bank statement row use, posting or transaction?
A bank statement row should use the date the bank shows as the date the money was credited or debited, usually labelled the posting date, because that is the date the statement's balances are built from. When the statement also prints a transaction date, keep it in a second column rather than choosing between them.
For consumer accounts, the rules point to the posting date. The Regulation E periodic statement rules in § 1005.9(b) require a statement to show, for each electronic fund transfer, "the date the transfer was credited or debited to the consumer's account." For any other statement, check how it labels its dates before choosing.
Card statements often print both dates. The Regulation Z § 1026.8 commentary on identifying transactions says a creditor that discloses more than one date, such as the transaction date and the posting date, must identify each. For a purchase made in person, the commentary defines the transaction date as the calendar date of the purchase.
The two dates can differ by a day or more, and the difference matters at the edges. A purchase made on March 31 that posts on April 2 can land on a statement covering April. If the sheet uses transaction dates and the firm's month-end cutoff follows the statement, that row appears to belong to the wrong month. Choose one date for the cutoff, say which one in a note on the sheet, and apply it to every account the same way.
Bank statement date format mistakes: swapped day and month
A swapped day and month is the bank statement date format mistake that does the most damage, because it produces real dates that are wrong. Read in month-first order, 04/05/2026 becomes April 5 when a day-first bank meant 4 May. Nothing flags it, and the rows land in the wrong month.
The giveaway is a column that is only partly converted. Day-first dates with a day of 12 or lower become real but swapped dates, while dates with a day of 13 or higher stay as text or show errors. Sort the column and look for a mix, or check any row dated after the 12th against the statement.
Fix the order at import. In Power Query, the Power Query data types documentation gives the method: right-click the column heading, choose Change Type, then Using Locale, and pick the data type Date with a locale that matches the bank, such as English (United Kingdom) for day-first dates. The Text Import Wizard page offers the same choice in the legacy wizard: set the column to Date and pick the date order that matches the bank's file.
Fixing swapped dates after the fact is harder, because the swapped dates are no longer text and the original string is gone. Go back to the source file and import again. The Power Query documentation also suggests adding day, month and year columns from the converted date to confirm each part landed where it should.
Dates are one of several things that go wrong in a conversion. The comparison of four ways to convert a bank statement to Excel shows which methods are most exposed, and how to convert a bank statement PDF to Excel covers the full PDF route. Rows headed for accounting software rather than a workbook have their own date settings, covered in the QuickBooks, Xero and Excel category. Schema CPA classifies every statement page by issuer and period before it exports the rows to Excel or CSV, and the hours calculator estimates what manual entry like this costs a firm now.