Fix Bank Statement Dates in Excel: Text, Year and Format

TL;DR Bank statement dates go wrong in Excel in four main ways: they arrive as text instead of real dates, they're missing the year, day and month get swapped because of a regional setting, or the year is wrong on statements that span December and January. Test with =ISNUMBER(A2), convert text with DATEVALUE, Text to Columns or DATE built from parts, add the year from the statement period, and use a formula that bumps January dates into the next year when a statement crosses New Year. Then format the column as yyyy-mm-dd so nothing is ambiguous.

Of all the problems you hit when getting bank statements into Excel, dates cause the most quiet damage. A wrong amount usually shows up when you check balances. A wrong date often doesn't. The total still adds up, but transactions land in the wrong month, sorting gets scrambled, pivot tables split one month into two, and an import into accounting software either fails or puts transactions in the wrong period.

This guide explains each common date problem, how to spot it, and how to fix it with formulas or built-in tools. The examples use Excel for Windows and Mac; most also work in Google Sheets with minor changes noted along the way.

First, how Excel stores dates

Understanding one thing makes every fix below make sense: Excel stores a date as a number. Each day is one whole number counted from a starting point, and the formatting you see (3/14/2026, 14-Mar-26, 2026-03-14) is just a display layer on top of that number.

That means:

• A real date is a number. It aligns to the right by default, you can add 1 to get the next day, and it sorts correctly.
• A text date only looks like a date. It aligns to the left by default, adding to it causes an error or does nothing useful, and it sorts alphabetically, so 10/01 sorts before 2/01.

The quickest test:

=ISNUMBER(A2)

TRUE means a real date. FALSE means text that needs converting. Run this down the whole column; a mix of TRUE and FALSE is common and tells you some rows converted automatically and some didn't, which is often a sign of a day/month problem (more on that below).

Problem 1: Dates arrived as text

This happens when you paste from a PDF, import a CSV with an unusual format, or a converter outputs dates as strings.

Fix with DATEVALUE

If the text is in a format your Excel regional settings recognize, DATEVALUE converts it:

=DATEVALUE(A2)

Format the result as a date. If you get #VALUE!, the text format doesn't match your settings, so use one of the methods below.

Fix with Text to Columns

This is the fastest fix for a whole column and it's often overlooked.

1. Select the date column.
2. Go to Data, Text to Columns.
3. Choose Delimited, click Next, clear all delimiters, click Next.
4. Under Column data format, choose Date and pick the order the text is in: MDY, DMY or YMD.
5. Click Finish.

Excel converts each text value using the order you specified, regardless of your regional settings. That last point is what makes this method so reliable for statements from another country.

Fix by building the date from parts

When the text is in an unusual layout, take it apart and rebuild it with DATE(year, month, day).

For text like 03/14/2026 (month first):

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

For text like 14.03.2026 (day first):

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

For text like 20260314:

=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

For text with a month name, such as 14 Mar 2026 or Mar 14, 2026, DATEVALUE usually works in English-language Excel. If it doesn't, Text to Columns with the right order, or a lookup of the month abbreviation, will.

If single-digit days and months aren't zero-padded (3/7/2026), LEFT and MID with fixed positions break. In Excel 365, split on the slash instead:

=LET(p, TEXTSPLIT(A2, "/"), DATE(INDEX(p,3), INDEX(p,1), INDEX(p,2)))

Problem 2: The year is missing

Many bank statements print transaction dates as 03/14 or Mar 14 with no year, because the year is shown once in the statement period at the top. When pasted into Excel, those either stay as text or, worse, Excel quietly assumes the current year.

That second case is dangerous. If you're converting last year's statements this year, every date becomes this year, and nothing looks broken. Always check the year after converting old statements.

Add the year with a formula

Put the statement year in a cell, say H1. Then for MM/DD text:

=DATE($H$1, LEFT(A2,2), MID(A2,4,2))

For Mar 14 text:

=DATEVALUE(A2 & " " & $H$1)

Fix dates Excel already assigned the wrong year

If Excel has converted 03/14 to a real date with the wrong year, rebuild it with the right one:

=DATE($H$1, MONTH(A2), DAY(A2))

Problem 3: Statements that cross New Year

A statement period of December 16 to January 15 contains dates from two years. If you add a single year to every row, half the statement is wrong.

The fix: use the statement's start and end dates. Put the period start in H1 and the period end in H2 as real dates. For a transaction with month and day available:

=LET(m, LEFT(A2,2)+0, d, MID(A2,4,2)+0,
     y, IF(m < MONTH($H$1), YEAR($H$2), YEAR($H$1)),
     DATE(y, m, d))

How it works: if the transaction's month is earlier in the calendar than the period's start month (January is earlier than December), it belongs to the end year; otherwise it belongs to the start year.

After converting, sanity check that every date falls within the period:

=AND(B2 >= $H$1, B2 <= $H$2)

Any FALSE needs a look. Some statements include an item dated slightly before the period, such as a transaction that posted late, so investigate rather than force it.

Problem 4: Day and month are swapped

This is the most confusing date problem because it's partial. It happens when a file uses one order (say day first, 14/03/2026) and your Excel regional settings use the other (month first).

Excel tries to interpret each value:

• 14/03/2026: there's no month 14, so it stays as text.
• 03/04/2026: valid either way, so it becomes March 4 when it should be April 3.

You end up with a column where some values are text and some are real dates, and the real ones are wrong. Sorting looks chaotic, and transactions land in the wrong month.

How to spot it

• ISNUMBER returns a mix of TRUE and FALSE.
• The real dates only ever have a "day" of 12 or less.
• Transactions appear to jump between months out of order.

How to fix it

The safest fix is to go back to the source and re-import with the correct order:

• Text to Columns on the original text, choosing DMY or MDY as appropriate.
• Power Query: when changing the column type, use "Using Locale" and choose the locale of the source data.
• CSV import (Data, From Text/CSV): set the file origin or locale before loading.

If you can't go back to the source, you can rescue the column. For a cell that has been wrongly converted to a date, swap day and month:

=IF(ISNUMBER(A2), DATE(YEAR(A2), DAY(A2), MONTH(A2)), DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)))

This assumes the source was day first. It swaps converted values back and parses text values correctly. Check the result by confirming every date falls inside the statement period.

Google Sheets

In Sheets, the equivalent setting is the spreadsheet locale (File, Settings). A sheet set to a US locale will read 03/04 as March 4. Either set the locale to match the file before importing, or import the dates as plain text and use the DATE formulas above. Our guide to converting a bank statement PDF to Google Sheets covers the import.

Problem 5: Dates include times or extra text

Some exports include a time (2026-03-14 00:00:00) or extra characters (14/03/2026 POSTED).

• Real date with a time: =INT(A2) removes the time. Format as a date.
• Text with a time: =DATEVALUE(LEFT(A2,10)) for ISO-style dates.
• Text with extra words: =DATEVALUE(LEFT(A2,10)), or TEXTBEFORE(A2," ") in Excel 365, then convert.

Transaction date versus posting date

Some statements, especially credit card statements, show two dates per line: the transaction date (when you made the purchase) and the posting date (when it hit the account). Decide which you'll use and be consistent.

• For reconciling and checking balances, the posting date usually matches how the statement itself is organized.
• For budgeting or understanding when spending happened, the transaction date can be more intuitive.
• For importing into accounting software, follow your bookkeeping policy, and use the same choice every month so items don't fall between periods.

Keep both columns if you have them. It costs nothing and answers questions later.

Pick an unambiguous display format

Once dates are real, format the column so nobody can misread it. The ISO format yyyy-mm-dd (for example 2026-03-14) is unambiguous in every country.

Select the column, open Format Cells (Ctrl+1 or Cmd+1), choose Custom, and enter:

yyyy-mm-dd

This matters when you share files internationally, and it's a good choice when exporting a CSV for accounting software, though you should always match the date format the import screen expects. Our guides to QuickBooks Online CSV import errors and Xero bank statement import errors both list date format mismatches as a leading cause of failed imports.

Fixing dates in Power Query

If you load statements through Power Query, whether from a PDF, a folder of CSVs or a workbook, fix dates there rather than with worksheet formulas. The fix then reruns automatically every time you refresh with new statements.

Change type using locale. Right-click the date column header, choose Change Type, then Using Locale. Set the data type to Date and the locale to the one the source uses, for example English (United Kingdom) for day-first dates or English (United States) for month-first. Power Query parses every value with that order, regardless of your computer's settings. This single step fixes most day/month swaps.

Add a missing year. If the column contains only month and day, add a custom column that combines it with the year from the statement period. If your query has a column holding the statement year, a custom column formula along these lines works:

= Date.FromText([DateText] & "/" & Text.From([StatementYear]), [Format="MM/dd/yyyy"])

Adjust the format string to match the text order. For statements that cross New Year, add a conditional step that uses the following year when the month is lower than the period's start month, mirroring the worksheet formula earlier in this guide.

Remove times. If dates arrive as date-times, change the type to Date and Power Query drops the time.

Watch for errors. After changing type, Power Query marks values it couldn't convert as Error. Right-click the column and choose Keep Errors to see exactly which rows failed. They're often stray header rows repeated on each page, or description fragments that landed in the date column. Remove them with a filter rather than by hand, so the cleanup is repeatable.

Our Power Query bank statement guide walks through building the full query.

Grouping by month once dates are real

The payoff for real dates is that summaries become easy. A few useful helper columns:

In a pivot table, you can also right-click a real date field and choose Group to group by months, quarters and years. That only works if every value in the column is a real date; a single text value stops grouping. If the Group option is greyed out or produces an error, run ISNUMBER down the column again to find the stray text value.

The yyyy-mm month label is worth highlighting. Labels like "March" or "Mar-26" sort alphabetically when stored as text, so April comes before January. 2026-01, 2026-02 and so on always sort in date order. Our bank statement pivot table guide shows how to use these columns in practice.

A worked example

A bookkeeper receives twelve months of statements for a client, converted by copy and paste from PDF into one sheet. The dates are a mess.

1. Test. ISNUMBER shows about 40% TRUE and 60% FALSE. All TRUE values have a "day" of 12 or less. Diagnosis: the source was day-first, and Excel is set to month-first.
2. Check the source. The pasted text shows dates as DD/MM with no year.
3. Rebuild. In a helper column she uses the statement period for each row (stored in columns H and I from a lookup on the source file name) and the New Year formula, parsing day first.
4. Validate. A period check confirms every date falls inside its statement period. Three rows fail. Each is a line where a description wrapped onto a second row and the second row picked up part of the description as a "date". She merges those rows.
5. Format. The column is formatted yyyy-mm-dd, and the helper column replaces the original using Paste Special, Values.
6. Balance check. With dates fixed and wrapped rows merged, each month's opening balance plus transactions equals its closing balance.

Most of the time was spent diagnosing. Once she recognized the day/month pattern, the fix took minutes.

Preventing date problems in the first place

• Convert PDFs with a tool that outputs real dates with years. SheetStatement fills in the year from the statement period, including periods that cross New Year, and exports real Excel dates. The free plan covers 3 pages a month.
• Import CSVs through Data, From Text/CSV rather than double-clicking them, so you can set the locale and column types.
• Keep one statement per block while converting, so each block's period is known.
• Always run a period check on converted dates.
• Run the balance check to catch wrapped rows, which often show up as date problems too. Our free balance checker helps.

For a broader cleanup routine covering amounts, descriptions and duplicates as well as dates, see how to clean bank statement data in Excel.

FAQ

Why are some of my bank statement dates left-aligned?

Left-aligned values are usually text, not real dates. Test with ISNUMBER and convert with DATEVALUE, Text to Columns or a DATE formula.

How do I add the year to dates like 03/14?

Put the statement year in a cell and build the date with DATE(year, month, day), taking month and day from the text. For statements crossing New Year, use the period start and end dates to choose the year.

Why did Excel swap my days and months?

The file uses a different date order from your regional settings. Values that work either way get swapped silently and the rest stay as text. Re-import with Text to Columns or Power Query using the correct order or locale.

Which date format should I use for importing into accounting software?

Use the format the import screen asks for. yyyy-mm-dd is unambiguous and widely accepted, but always match the software's setting.

Should I use the transaction date or the posting date?

Either can be right; be consistent. Posting dates usually match the statement's balances, while transaction dates show when spending actually happened.

Do these formulas work in Google Sheets?

Most do. DATE, DATEVALUE, LEFT, MID and RIGHT work the same. Text to Columns is "Split text to columns", and the locale under File, Settings controls how dates are interpreted.