Bank CSV dates and numbers: why 03/04 and 1,234 need a closer look

Keep the raw text so you can check your conversions

These checks cover bank and credit-card CSVs, using Excel for Microsoft 365 on Windows. The Power Query guidance also lists Excel 2024 and 2021. QuickBooks means Intuit's US QuickBooks Online, not Desktop or every country edition. Sources checked on October 17, 2026 describe workflows; we did not test a live Excel or accounting-app import.

You need the untouched CSV and statement PDF to review what changed. Save those, then use a separate review workbook with source row numbers, raw dates and raw amounts beside your conversions. That lets you review the changes before exporting only the columns your destination needs.

Start by looking at the file as text, because Excel may already have converted a displayed 03/04/2026. Microsoft's CSV import guide, updated June 25, 2026 says opening a CSV uses the current default data-format settings. Changing the display afterward won't tell you whether the source meant March 4 or April 3.

A day greater than 12 gives you something to go on

There is no thirteenth month, so a day value greater than 12 identifies the day position in a valid numeric date with a known year position. That means 13/04/2026 must be day/month/year, while 04/13/2026 must be month/day/year. You now know this row's order, but not the convention of an entire merged file.

This fictional eight-row USD bank CSV comes from a statement covering April 1 through April 30, 2026. Both readings are shown without switching conventions for failing rows.

Rows 5 through 8 prove day-first order for those rows, and all eight then agree with the fictional statement's period and printed dates. Row 2 tells you nothing, because swapping equal values changes nothing. Rows 1, 3 and 4 are valid but wrong under the other reading.

A preview won't settle this for the whole column. If it contains both 13/04/2026 and 04/13/2026, stop: there may be mixed conventions or an error. Separate the rows by original source so you can resolve them there. Impossible dates such as 31/04/2026 must be rejected, too. Day-first ordering doesn't give April 31 days.

If every day is 12 or less, check outside the CSV

Dates such as 03/04/2026 cannot establish their own convention, however familiar one reading looks. Your computer's country setting isn't evidence either, so you'll need to look beyond the file.

1. Match a row to the statement, ideally using a printed date with a month name, or a bank export specification naming the order. A PDF with the same ambiguous digits won't settle anything without more context.
2. Compare both possible date ranges with the statement period. If a date falls outside it, check whether it is a transaction, posting or value date before rejecting it.
3. Check the original sequence before sorting anything. A file you know is ordered by posting date should stay ordered after conversion, but sorting it yourself proves nothing. Some sequences stay ordered under both readings.
4. If both readings still fit, leave the dates unresolved while you ask the bank or source provider. Uploading a guess doesn't make it a verified correction.

Use the statement period to expand two-digit years before parsing. Microsoft's two-digit-year guidance, updated July 23, 2026, describes the 2029 cutoff: 00 through 29 become 2000 through 2029, while 30 through 99 become 1930 through 1999. It distinguishes typed dates, whose cutoff Windows regional settings can change, from imported dates. Parsers don't necessarily share a cutoff, and software rules cannot prove an archive's intended year. Use four digits instead.

When dates have no year, the statement period matters, not today. In a fictional December 15, 2025 through January 14, 2026 statement, confirmed day-first dates 28/12 and 03/01 become December 28, 2025 and January 3, 2026. Giving every row the closing year would get this wrong. If the file spans several years, a month and day may fit more than one year, so recover the original period boundaries before combining files.

Time zones and month names need their context

A date-only bank posting date doesn't call for time-zone conversion. Timestamps need more care: keep the raw value and offset until you know which date the destination needs. Under RFC 3339, section 4.2 (July 2002), the synthetic timestamp 2026-01-01T00:30:00Z is the same instant as 2025-12-31T19:30:00-05:00. Taking the date before or after conversion crosses a year boundary.

Neither is automatically the bank's posting date, so use its stated date field or documented time zone. If a timestamp has no offset, ask the source rather than applying your laptop's zone. Don't shift a printed date to match another timestamp.

Month names settle day/month order only if you understand the language and abbreviation. In a confirmed English-language source, 03 Apr 2026 means April 3, 2026. Keep that original spelling while you choose the source locale and check the resulting month. Stripping all letters would destroy month names along with unwanted weekday text.

Intuit's CSV formatting help, updated August 24, 2026, says to remove day-of-week text from the Date column and use one date format. For a confirmed day-first 03/04/2026 FRI, that means keeping the raw value in your review copy and sending 03/04/2026 to the importer. Splitting blindly on spaces won't do, because month-name dates and timestamps contain them too.

Intuit recommends dd/mm/yyyy in that guide, while its common-errors guide, updated August 3, 2026, says the file must match the date format selected during mapping. You therefore need to establish what the source means before formatting it for the destination. The QuickBooks CSV guide covers the remaining upload checks.

A comma can change the amount you're importing

Decimal separator, grouping, sign and currency each need a separate check. With dot decimals and comma grouping, 1,234.56 means 1234.56. With comma decimals and dot grouping, 1.234,56 means the same. But 1,234 can mean 1234 or 1.234, and 1.234 has the reverse ambiguity. The magnitudes differ by a factor of 1000.

Rows with explicit fractional parts, the bank's export settings and independently readable balances can help you establish the convention. Three digits after punctuation don't always mean grouping, so don't force two decimal places before you know what that punctuation means.

• If the source confirms grouping, 1'234.56 and 1 234,56 can both become 1234.56. Identify the actual separator, including nonbreaking spaces, so you can remove it without deleting characters across the file.
• If the source defines (25.00) or 25.00- as negative, use -25.00, not 25.00, so removing the wrapper doesn't lose the sign. Microsoft's Text Import Wizard documentation, updated August 12, 2026 has a trailing-minus option under Advanced. This is not the Power Query menu.
• To read CR and DR, use the statement's legend and account perspective. Deleting a suffix doesn't justify treating the number as positive. NatWest International's abbreviation key, published February 27, 2024 defines DR as account overdrawn or debit item, so check whether it labels a balance or transaction. For cards, use the credit-card sign checks.
• Removing a currency symbol for a numeric import field isn't currency conversion. Confirm and record the currency first, and keep different currencies out of the same total.

Excel's NUMBERVALUE documentation, updated June 25, 2026 describes decimal and grouping arguments. Here's how those work for text in A2:

These formulas use comma argument separators. Microsoft's formula-separator guidance, updated April 15, 2026 says to use your regional list separator. If Excel expects semicolons between arguments, change those, not the quoted punctuation. The rule still needs to come from the source, because getting a number back doesn't prove it's right.

Microsoft also says an empty string becomes zero, spaces between digits are ignored and trailing percent signs scale the result. Its examples include 3 000 becoming 3000 and 9%% becoming 0.0009. NUMBERVALUE converts text, but it doesn't validate complete bank amounts. That's why raw blanks, unexpected percent signs and grouping need checking first. Leave failed conversions visible rather than hiding them behind zeros just to get a total.

Only one reading matches these balances

The Amount values from the same fictional eight-row April CSV show why the convention matters. Its statement prints an opening balance of USD 2,000.00 and closing balance of USD 2,675.00, read independently rather than parsed with the convention under test. Positive means money into this bank account, while negative means money out.

With comma decimals, 1,250 equals 1.250, exactly 1.25, with no fractional-cent rounding involved. Every other row is an integer either way. Comma grouping gives 1250 - 250 - 125 + 50 - 75 - 25 - 100 - 50 = 675. A decimal comma gives 1.25 - 250 - 125 + 50 - 75 - 25 - 100 - 50 = -573.75.

Only grouping matches the independently supplied balances. The synthetic statement also prints 3,250.00 after row 1, whereas comma decimals produce 2,001.25. That pinpoints the first disagreement, but you can't make this row-level check without source running balances.

In a comma-delimited CSV, an amount containing a comma needs to stay in one field, for example, "1,250" with a double-quote text qualifier. That comma belongs to the amount, not a new column, as Microsoft's Text Import Wizard guide explains. Replacing commas across the whole file can change column separators and descriptions too, so leave those alone.

Excel's import preview gives you a chance to check

The import preview is where you can check that descriptions and grouped amounts stay in single cells. Choose Data → From Text/CSV and the delimiter that gives you the right columns, following Microsoft's CSV import guide above. Before loading, open the query editor through Edit or Transform Data, depending on the dialog.

1. Keep raw dates and amounts as Text until you resolve them, and account references and transaction identifiers as Text permanently. If a type was inferred, choose Text and Replace Current, because a later Text step won't undo a damaging numeric conversion. Duplicate the raw date and amount columns so you have separate columns to convert.
2. The working date column needs the proven source convention. Microsoft's Power Query locale guide, updated September 17, 2026 says to right-click the header, choose Change Type → Using Locale, then select the type and source locale, not the destination reader's preference.
3. For the working amount column, resolve parentheses, trailing minus signs and CR/DR first, keeping negatives as leading-minus values. Then use Change Type → Using Locale with a numeric type and confirmed source locale. Before totaling, review errors and compare the counts of blanks and parsed amounts with the source transaction count.
4. Compare converted dates with the statement and recompute balances, then save the review workbook separately. Reopening it successfully doesn't verify the CSV, so inspect the exported text and the accounting app's mapping preview as well.

A Change Type locale overrides the query locale, which overrides the operating-system locale. You can therefore set it per column without changing your whole computer for one file. When a source combines different exports, decide date and number conventions separately.

Identifiers aren't amounts. Microsoft's leading-zero and large-number guidance, updated June 25, 2026 says Excel numbers have 15 significant digits of precision. References with 16 or more digits can lose digits beyond that limit, while 001234 can lose leading zeros. Import both as Text from the original, because formatting cannot recover lost digits. Scientific notation is a reason to inspect the value, not proof of damage.

Stop the upload if a date is still a guess

Before uploading, you should be able to explain your day/month order, four-digit years and time zones. Record that evidence alongside separate rules for decimal separators, grouping, currency and signs.

Mixed or unresolved dates mean the affected file isn't ready to upload. Dropping those rows wouldn't leave a complete statement, and inventing zeros or balance adjustments wouldn't resolve blanks and errors. Compare every row with its source before checking closing and available running balances. Then inspect the final CSV and destination preview, including an ambiguous date and amount.

The QuickBooks CSV checker checks dates and numbers for import preparation, while the statement balance checker compares interpreted amounts with source balances. Both need the conventions you established, because neither can supply missing evidence. Follow the broader bank CSV review checklist before importing.

A usable native bank export can be corrected manually. If your rows are still trapped in a PDF, you can transcribe them into this workflow or convert with AnyStatement. Where source positions are available, compare the source-linked transactions with the PDF. You still need to resolve ambiguous source text.

What we checked, and what we didn't test

On October 17, 2026, we opened the linked Intuit US formatting and import-error pages; Microsoft's CSV import, Text Import Wizard, locale, two-digit-year, identifier, formula-separator and NUMBERVALUE documentation; NatWest International's abbreviation key; and RFC 3339. These describe how the software and formats behave, not universal rules for bank CSVs.

We built the fictional eight-row example and recomputed both amount readings, sums, closing differences and running balances independently with Python Decimal. Python's date/time library checked both date readings, December/January examples, the weekday suffix and UTC-offset conversion. A separate script modeled the selected documented NUMBERVALUE and century-cutoff rules, rather than testing Excel itself.

No real customer data was used. We didn't run Excel, execute NUMBERVALUE, import into QuickBooks or Xero, or test a product conversion. The checks cover source interpretation and statement arithmetic, not bookkeeping reconciliation or authenticity.