Dates and numbers that lie

The core date ambiguity

Written as three numbers, a date can be day-first or month-first, and both are entrenched conventions used by hundreds of millions of people. When the first two components are both twelve or below, the value is genuinely ambiguous — not obscure, not edge-case, simply ambiguous.

Roughly a third of dates in any year fall into this trap, since it needs only that both the day and the month be twelve or less. For those dates, a parser configured for the wrong convention produces a real date in a real month with no complaint whatsoever.

The resolution is to stop looking at the value and look at the column. If any value in the column has a first component above twelve, the file must be day-first, because there is no thirteenth month. One such value settles the entire file.

In a statement with a few dozen transactions spread through a month, such a value is almost always present — any transaction after the twelfth provides one. That is why column-level inference works so well in practice while value-level parsing does not.

flowparse.io

When it genuinely cannot be resolved

Occasionally there is no disambiguating value. A quiet account with four transactions, all in the first twelve days of the month, produces a file where every date is valid under both readings and nothing in the data prefers either.

This is worth being honest about, because the temptation is to pick the more likely convention and proceed silently. That produces a result that looks exactly as confident as a resolved one, which is precisely the property you do not want an unresolved question to have.

Use external knowledge where it exists — the bank's country, the account's currency, other files from the same source that were resolvable. Those are strong signals and entirely legitimate. What matters is that using them is a recorded decision rather than a default.

And surface it. A note saying that a convention was assumed because the file could not resolve it costs nothing and turns an invisible risk into a visible one. Someone who knows the account can confirm it in seconds.

Two-digit years

A two-digit year needs a rule about which century it belongs to, and every such rule is an assumption dressed as logic. The common approach — a pivot year, below which the year is treated as this century and above which as the last — works for most data and is wrong for some.

Financial history is exactly the domain where it can be wrong. Records genuinely span decades, and an archive containing both recent statements and much older ones will have values on both sides of any pivot you choose.

Prefer four-digit years wherever the source provides them, which is most of the time in modern exports. Where it does not, apply a rule that suits the actual data range rather than a general default, and record which rule you applied — for the same reason as every other assumption here.

Dates with no year at all

Printed statements often omit the year on individual rows, since the statement period is stated in the header and repeating it on every line would be noise. Perfectly sensible for a reader, and a real problem for a machine.

Taking the year from the header works for most statements and fails at exactly the boundary that matters. A statement covering late December into early January contains dates from two different years, and stamping the header year onto all of them puts several transactions twelve months out.

The fix is to use the statement period rather than a single year: walk the rows in order, and when the month goes backwards — from December to January — increment the year. Because statements are chronological, that simple rule handles the boundary correctly.

It is a small piece of logic and it prevents an error that is otherwise both severe and seasonal, arriving precisely when everyone is closing a year.

flowparse.io

Month names and locales

A spelled month is a genuine improvement, because a name cannot be mistaken for a day. Any statement using them has eliminated the central ambiguity at the source.

They bring smaller problems. Abbreviations vary in length and punctuation. Languages differ, and a converter meeting a statement in an unfamiliar language may not recognise the month at all. Some locales capitalise month names and others do not, and some use genitive forms that differ from the dictionary form.

But notice the shape of these failures: they are noisy. An unrecognised month name fails to parse, which raises something. That is categorically better than a numeric date silently read under the wrong convention, and it is why month names are the safer choice whenever a format is being designed rather than merely consumed.

flowparse.io

Which date is it, anyway

Ambiguity of format is one problem. Ambiguity of meaning is another, and statements frequently carry more than one date per transaction.

A booking date is when the entry was recorded on the account. A value date is when it counts for interest. A transaction date may be when a card was used, which can be days before either. All three are legitimate and they differ, most visibly across weekends and holidays.

For reconciliation and posting, the booking date is normally the right choice, because it is the date the bank considers the movement to have happened. What matters more than the choice is consistency: mixing bases produces small period-end differences where every individual entry is correct, which is a genuinely tedious thing to diagnose.

In structured formats these are separate labelled elements, which removes the guesswork entirely — one of the concrete advantages described in CAMT.053 explained. In a PDF with two date columns and no headings, deciding which is which is one more inference.

Decimal separators

The second great divide. A full stop separates decimals in some conventions and groups thousands in others, and the comma does precisely the opposite. Both are used across large parts of the world, and bank exports follow the locale of the bank rather than of the reader.

Getting it wrong is severe — a factor of a thousand, or decimals absorbed into the integer part — and mercifully obvious. A balance check catches it on the first row. Of all the problems in this article, this is the one most likely to be caught quickly, which is a small consolation.

Detection is reliable: look at the last separator in a value. If exactly two digits follow it, it is the decimal separator. Currency amounts almost always carry two decimal places, so this holds nearly universally for money.

flowparse.io

Then, if a different separator appears earlier in the same value, that one is the grouping separator — and you have resolved both conventions from a single value, with the rest of the column available to confirm it.

The 1.234 problem

One case defeats the rule above, and it is worth understanding because it is common. A value with a single separator followed by three digits could be one thousand two hundred and thirty-four with a grouping separator, or one point two three four with a decimal separator.

For currency, three digits after the separator is strong evidence of grouping, since money is conventionally written with two decimal places. That heuristic is right nearly always and is worth applying — while remembering it is a heuristic and not a proof.

The column resolves it properly. If other values in the same column show two digits after a separator, that separator is decimal and the three-digit case must be grouping. If a value shows both separators, their roles are fixed by position. Across a few hundred rows, the convention becomes unambiguous even though individual values are not.

Beyond those two, grouping can also be a space, a narrow non-breaking space, or an apostrophe depending on locale. Strip whatever the grouping separator turns out to be before parsing, rather than assuming the set of possibilities is small.

Five ways to be negative

Direction is expressed in at least five different ways across financial documents, and most number parsers accept exactly one of them.

A leading minus is the case everything handles. A trailing minus is common in output from older accounting systems and is silently mishandled by strict parsers. Parentheses are the accounting convention and are usually parsed as text or rejected. A debit or credit suffix puts the direction in letters. And separate columns remove the sign from the value entirely.

The failure mode is uniform across all of them: direction is lost, payments read as receipts, and the resulting difference is exactly twice the affected amounts — the arithmetic fingerprint described in why reconciliations fail.

Detect the convention across the column and confirm it against the balance movement rather than configuring it once. A file where every value is positive is not necessarily a file of receipts — it may be a file whose sign convention you have not noticed yet.

flowparse.io

Currency attached to the value

Amounts often arrive with a symbol or code attached, sometimes before the number, sometimes after, sometimes with a space and sometimes without. Strip it before parsing — but capture it first, because it carries information you need.

Symbols are weak evidence. Several currencies share the dollar sign, and position varies by locale rather than by currency. A three-letter code is unambiguous and should be preferred wherever the source provides one. Where only a symbol exists, treat it as a hint to be confirmed by the account rather than as a determination.

Always store the currency with the amount. An amount without one is not a quantity of money, and the moment an account holds more than one currency — or a card statement includes a foreign transaction alongside its converted value — the omission stops being theoretical.

Watch for the pattern where a foreign amount and a converted amount both appear on the same row. Reconciliation needs the account currency; the foreign value is context. Taking whichever appears first is how a statement quietly reconciles against the wrong numbers.

Never store money in a floating-point number

This one is not about source formats at all — it is about what happens after parsing, and it is the most common serious mistake in financial software written by people who have not been bitten yet.

Binary floating point cannot represent most decimal fractions exactly. A tenth has no exact binary representation, any more than a third has an exact decimal one. So adding a tenth to two tenths does not produce exactly three tenths — it produces something minutely larger, and the residue is real.

One such residue is invisible. Thousands of them, accumulated across a year of transactions, produce a total that is out by a cent or two. And a one-cent discrepancy is indistinguishable from a genuine error, so somebody spends an afternoon establishing that the arithmetic was never wrong in the first place.

Use integer minor units — store cents rather than currency units — or a decimal type that represents decimal fractions exactly. Both approaches are well supported everywhere, and both remove the problem entirely rather than reducing it.

flowparse.io

Detecting conventions from the column

The unifying technique, stated once: infer conventions from the whole column, then apply them uniformly to every value, and reject the file if any value contradicts the inference.

That last clause carries most of the safety. If you have concluded the dates are day-first and one value has a first component above twelve while another has a second component above twelve, the column is not internally consistent — and rather than parsing each row under whichever reading works, which produces a silently mixed result, the correct action is to stop.

Mixed conventions within one column do happen, usually where a file was assembled from two sources. It is genuinely a broken file, and treating it as one is much better than heroically parsing it into something that looks fine.

The same principle applies to numbers. Infer the decimal and grouping separators from the column, apply them everywhere, and treat a value that does not fit as a reason to stop rather than as a special case to accommodate.

The arbiter

Where a statement carries a running balance, all of this becomes testable rather than merely careful. For each row, the change in balance from the previous row should equal that row's amount.

Run that down the file and you have confirmed, in a single pass, that the decimal convention is right, that the sign handling is right, that the grouping separator was stripped correctly, and that the rows are in the order you think they are. Four questions, one check, using nothing but the document's own internal consistency.

The totals identity — opening balance plus every transaction against the printed closing balance — adds an independent second check with a different blind spot, since the row-to-row chain closes over a row that vanished entirely while the totals identity does not. Run both.

Note what neither one tests: dates. The arithmetic is completely indifferent to when a transaction happened, so a file with every date misread will balance perfectly. Dates need the column-level inference; there is no arithmetic that will rescue them.

flowparse.io

What to store once you have resolved it

Resolve the ambiguity once, at the boundary where data arrives, and store values in forms that cannot be reinterpreted. Every downstream reinterpretation is another chance to get it wrong differently.

Dates as ISO 8601 — year, month, day, in that order — or as a proper date type. The ordering makes the components unambiguous and sorts correctly as text, which is a genuinely useful side effect.

Amounts as integer minor units or a decimal type, never as a float, and never as a formatted string. Formatting is a presentation concern and belongs at the point of display.

Currency as an explicit three-letter code alongside every amount, not inferred from the account and not carried as a symbol.

The original as it arrived, unmodified. When a question arises months later, the only way to determine whether an error came from the source or from your processing is to re-parse the original — which turns an unresolvable argument into a test.

A worked example

A firm imports statements for a client with an account abroad. The import has run for months without complaint. At the year end, the figures do not agree with the client's own records, and only in two months of the year.

Every check passes. The balances reconcile perfectly, the totals identity holds on every statement, no rows are missing. The arithmetic is impeccable, which is exactly what makes the problem hard to see.

The cause is dates. The import was configured for one convention while the bank exports in the other, so every date with both components at twelve or below is being read wrongly. Transactions on the fourth of a month appear on the fourth of a different month — and because the amounts are untouched, every balance check in the pipeline is satisfied.

The two conspicuous months are simply where the displaced transactions happened to be largest. The other ten are wrong too, by smaller amounts nobody questioned.

Column-level inference would have caught this on the first file: any transaction after the twelfth of a month has a first component above twelve, which is incompatible with the configured convention. One check, on file one, instead of a year of quiet displacement.

Ambiguity reference

Every ambiguity in this article, what resolves it, and how loudly it fails when it is not resolved.

flowparse.io

Key takeaways

Format ambiguity is dangerous precisely because it produces valid output. A misread date is a real date, a misread amount is a real number, and nothing in the pipeline objects. The defence is to infer conventions from whole columns rather than parsing values in isolation, and to stop when a column is not internally consistent.

Amounts have an arbiter: the balance column confirms decimal convention, sign handling and row order in one pass. Dates have none — arithmetic is indifferent to when things happened — which is why the column-level date inference has to be right rather than merely checked afterwards.

Resolve once at the boundary, store ISO dates, integer minor units and explicit currency codes, keep originals unmodified, and record any assumption you had to make. An assumption someone can find later is a completely different thing from one nobody knows was made.

Frequently asked questions

Related reading