The Settlement File Totalled 577,574.80 in Munich and 1,526.95 in Manchester, Chicago Moved Half of August Into Ten Other Months, and Nobody Edited a Single Cell

The file is called Settlement_2026_08.csv. Munich exports it on the first working day of the month, Manchester books it into the group ledger, Chicago uses it for the intercompany reconciliation. Twenty lines, 577,574.80 of parts, and it has worked for years — which is to say nobody has checked it for years.

Nobody edited a cell. Nobody has a different version. There is one file, and these are the numbers three people read out of it on the same morning.

A number is not stored as text and a date is not stored as a date. In a workbook, a number is a binary double and a date is a serial count of days, and neither of them carries a thousands separator, a decimal separator or a day-month order — those are display, applied afterwards. So a value that is already a value travels perfectly. The trouble is that a CSV contains no values at all. It contains text, and text becomes a value only at the moment something parses it, and parsing is the one part of Excel that asks the operating system rather than the file.

What this covers. Everything here behaves the same in Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac, with the exceptions noted where they arise: NUMBERVALUE needs Excel 2013 or later, and TEXTBEFORE/TEXTAFTER are 365 and 2024 only. SUM, COUNT, COUNTA, COUNTIF, COUNTIFS, SUMIF, SUMIFS, SUMPRODUCT, AVERAGE, MIN, MAX, VALUE, TEXT, DATE, DAY, MONTH, YEAR, EOMONTH, LEFT, RIGHT, MID, LEN, TRIM, SUBSTITUTE, ISNUMBER, ISTEXT, IF, IFERROR, XLOOKUP, INDEX/MATCH, FILTER, UNIQUE, SORT and LET are used as they work everywhere. An intercompany settlement is the example because the same file is read by three people with three keyboards, but this is the same job as a price list from an overseas supplier, an export from a payroll bureau, a bank statement, a lab instrument dump or any sheet that started life somewhere else.

1) One File, Three Totals

Here is the first line of the file, as bytes:

SET-3101;Bearing housing 60 mm;12.480,50;03/08/2026

In Munich that is twelve thousand four hundred and eighty euros and fifty cents on the third of August. It is also, letter for letter, what Manchester and Chicago receive. The three of them disagree because each one hands the string 12.480,50 to a parser configured by Windows, and the three parsers were configured by three different regional settings.

• Munich (de-DE): thousands ., decimal , → the number 12,480.50.
• Manchester (en-GB): thousands ,, decimal . → 12.480,50 is not a number. Excel keeps it as text.
• Chicago (en-US): identical to Manchester on numbers, different on dates.

The file did not change. The reading changed, and reading is not recorded anywhere, which is why this class of error has no audit trail: there is no before and after, only two people looking at one thing and seeing two things.

🎯 Scenario: The question to ask of any file that arrives from outside your own building is not "does it open?" — it always opens. It is "who decided what these characters mean?" If the answer is "whichever machine happened to double-click it", then the file has no fixed meaning and the number on your screen is a property of your desk.

2) The Point That Divides by a Thousand

Twelve of the twenty amounts have both separators, so Manchester rejects them outright and leaves them as text. That is the loud failure. The quiet one is the other five.

written in Munich   means      Manchester reads
3.500               3,500.00            3.500
18.200             18,200.00           18.200
2.450               2,450.00            2.450
1.075               1,075.00            1.075
6.720               6,720.00            6.720
                 -----------        ---------
                   31,945.00           31.945

Every one of those five is a valid number. Nothing is flagged, nothing is left-aligned, nothing is wrapped in a green triangle. They sit in the column looking exactly like the three rows that are genuinely correct, and they are wrong by a factor of one thousand.

And the three that are genuinely correct — 940, 75 and 480, 1,495.00 between them, 0.26% of the file — are correct for no better reason than that they happen to be under a thousand and so carry no separator at all. Had the grease cartridges cost 1.940 instead of 940, that row would have joined the other five.

🎯 Scenario: A whole-number amount with a single dot three digits from the end is the most dangerous string in cross-border data, because both readings are legal and only one is flagged. When you receive a column of amounts and every value is suspiciously small and suspiciously precise — 3.5, 18.2, 6.72 where you expected thousands — you are not looking at rounding. You are looking at a thousands separator that was read as a decimal point.

3) The Twelve That Are Not Numbers, and the Check That Cannot See Them

The remaining twelve rows — 544,134.80, 94.21% of the file — come in as text, and SUM does not ignore them as a courtesy. SUM cannot see them at all: text arguments in a range are skipped by every numeric aggregate in Excel, silently, by design.

=COUNTA($D$2:$D$21)    → 20     every cell has something in it
=COUNT($D$2:$D$21)     →  8     eight of those things are numbers
=SUM($D$2:$D$21)       →  1,526.95
=AVERAGE($D$2:$D$21)   →    190.87     the true average is 28,878.74

COUNTA and COUNT differing by twelve is the single most useful fact available about this file, and nobody runs it, because COUNTA on its own returns 20 and 20 is the right answer to the question everyone actually asks: did all the lines arrive?

They did. They arrived and then twelve of them stopped being money.

The largest line in the file is SET-3117, a twelve-metre conveyor at 245,900.00 — 42.58% of the settlement on its own. It is text. SUM has never seen it.

🎯 Scenario: Put =COUNT(range)&" of "&COUNTA(range) in a cell above every imported numeric column and leave it there forever. It costs one cell, it reads "8 of 20" on the morning this happens, and it is the only check in this article that requires no knowledge of which country the file came from.

4) Text Is Greater Than Every Number

The settlement has an approval rule: anything over 10,000 needs a second signature. Two people wrote two checks for it, and the checks disagree, and neither of them is broken.

=COUNTIF($D$2:$D$21,">10000")           → 0
=SUMPRODUCT(--($D$2:$D$21>10000))       → 12
the truth                               → 7

Both formulas are correct implementations of two different rules, and the rules differ because Excel has two ways of deciding whether a thing is bigger than 10,000.

COUNTIF with a numeric comparison criterion only considers numeric values. Text is not smaller and not equal — it is not eligible, so all twelve text rows are skipped, and of the eight numbers that remain the largest is 940. Zero.

A direct comparison with > does not skip anything, because Excel defines an order across types: every number is less than every text value, which is also why a mixed column sorts with its numbers first. So "12.480,50">10000 is TRUE, and so is "88,60">10000, a row that is really worth 88.60. Twelve.

Seven rows are genuinely over 10,000, worth 546,241.55. Neither check found them, and the one that reported zero is the one that reads like a clean bill of health.

🎯 Scenario: Whenever two checks over the same range disagree, do not pick the one you prefer — the disagreement itself is the finding, and it almost always means the range holds a mix of numbers and text. =SUMPRODUCT(--ISTEXT(range)) will tell you in one cell how much of the mix there is.

5) Half of August, Somewhere Else

Chicago's amounts break exactly as Manchester's do. The dates break differently, and worse, because a wrong date produces another perfectly good date.

The file writes dates as DD/MM/YYYY. An en-US Excel reads MM/DD/YYYY. When the day is 12 or lower, both readings are possible, Excel takes its own, and nothing is said:

03/08/2026  →  8 March 2026        the 3rd of August became the 8th of March
05/08/2026  →  8 May 2026
09/08/2026  →  8 September 2026
11/08/2026  →  8 November 2026
12/08/2026  →  8 December 2026

When the day is 13 or higher there is no second reading — no calendar has a seventeenth month — so Excel falls back to the only interpretation that works and gets it right:

17/08/2026  →  17 August 2026      correct
25/08/2026  →  25 August 2026      correct
31/08/2026  →  31 August 2026      correct

Ten of the twenty lines move. They are worth 312,700.85, 54.14% of the settlement, and they land on the 8th of March, April, May, June, July, September, October, November, December and February — ten different months, not one of them August. The other ten are perfect. A column where every value is wrong gets noticed on the first scroll; a column where half the values are wrong and all twenty are plausible dates in the right format does not.

Chicago's month-end filter, =SUMIFS($D:$D,$F:$F,">="&DATE(2026,8,1),$F:$F,"<="&DATE(2026,8,31)), is written perfectly and returns a perfectly wrong answer, because the flaw is upstream of it. =MIN($F$2:$F$21) reports the settlement's earliest invoice as 8 February 2026, six months before the period began, and that is the one symptom anybody might have noticed.

🎯 Scenario: Count your own ambiguity before you trust an imported date column: =SUMPRODUCT(--(DAY($F$2:$F$21)<=12)) returns 10 here. That is not a count of errors — it is a count of the rows that can be wrong without anything erroring. If it is zero, the column is safe whatever the locale. If it is most of the column, you know nothing about those dates until you know who parsed them.

6) What Travels in the File, and What Is Re-Decided on Arrival

This is the whole model, and it is short enough to memorise.

Travels intact, everywhere, always:

• Numbers. Stored as binary doubles. 12480.5 is 12480.5 in Riyadh, Lima and Oslo.
• Dates and times. Stored as serial numbers — 46,617 is the 3rd of August 2026 whether the machine writes it 03/08, 08/03 or 2026-08-03.
• Formulas. The file format stores them with English function names and commas, always. SUMIFS and SUMAR.SI.CONJUNTO are one function with two display names, and the semicolons a German colleague types are never in the file.
• Number formats. A format code such as #,##0.00 is stored with , and . as placeholders meaning "thousands separator here, decimal separator here", and each machine renders them with its own characters. That is why the same cell shows 12,480.50 in Manchester and 12.480,50 in Munich and is the same number in both.

Re-decided on arrival, by the machine, not the file:

• Any text that has to become a number or a date. CSV cells, pasted text, typing, VALUE, DATEVALUE.
• Criteria strings inside COUNTIF, SUMIFS and friends — section 10.
• Format codes inside TEXT() — section 9.
• The delimiter a CSV is split on, and written with — the Windows list separator, section 7.

So: an .xlsx is locale-safe and a .csv is not. Everything in this article happens because a settlement that is fully computed, fully typed and fully unambiguous inside a workbook is flattened into characters on the way out and has to be guessed back into values on the way in.

🎯 Scenario: If you control both ends of a recurring handover, stop sending CSV. An .xlsx of the same twenty rows would have opened identically on all three desks, with no settings to agree and no conversation to have. When you cannot control both ends — and you usually cannot — assume every text column is ambiguous until something in your own file makes it definite.

7) The Semicolon Is Not a Different Excel

Open a workbook in Munich and the formulas read =SUMIFS(D2:D21;C2:C21;"Bearing"). This alarms people, and it is the least important thing in this article.

Windows derives the list separator from the decimal separator: where the decimal is a comma, the argument separator becomes a semicolon, because a comma cannot do both jobs at once. Excel displays and accepts formulas using whatever that local separator is, and stores commas in the file regardless. Nothing is converted, nothing is at risk, and a workbook passed back and forth never accumulates damage.

Three places where it does bite:

1. Pasting formula text. A formula copied from an email, a blog or a chat message is a string, and a string written with commas will not parse on a machine expecting semicolons. This is the entire reason the separator has a reputation.
2. Formulas built as text. Anything that assembles a formula from strings — INDIRECT, a VBA .Formula assignment, a code generator — is writing for one convention. .Formula wants US syntax; .FormulaLocal wants the user's.
3. Array constants. Where the decimal is a comma, the column separator inside {…} is a backslash and the row separator is a semicolon, so {1,2,3} is typed {1\2\3}. Constants pasted from elsewhere fail for a reason that looks like nothing at all.

And one that catches everybody: Excel's "CSV (Comma delimited)" export writes the list separator, not a comma. Save that settlement in Munich and you get a semicolon-delimited file with .csv on the end, which is exactly what the sample at the top of this article is.

🎯 Scenario: Never move a formula between machines as text when you can move it as a file. If you must — a support ticket, a documentation page — say which convention you wrote it in, and expect the reader to swap it. And when a "comma-delimited" file turns out to be full of semicolons, nothing is corrupt: you are reading a file written by a machine whose comma was busy.

8) Function Names Are a Display Language

Excel translates function names in the interface for a number of languages. The workbook is not translated, because the workbook never held the translation: it holds SUMIFS and the UI paints SUMAR.SI.CONJUNTO over it.

Two consequences, both mild and both worth knowing:

• A formula typed in the wrong language gives #NAME?. Type =SUMAR.SI.CONJUNTO(...) into an English Excel and it is not a function, it is an undefined name.
• A formula pasted as text carries its language with it, which is the same problem as the semicolon and has the same cure.

In Microsoft 365 the formula language is a setting of its own (File → Options → Language), independent of the display language, which is the practical answer for anyone who works in one language and reads documentation in another.

The one case where a name really is stored is newer functions in older Excel: a file using XLOOKUP opened in Excel 2016 shows _xlfn.XLOOKUP and returns #NAME?. That is a version problem wearing a language problem's clothes, and it does not go away by changing a setting.

🎯 Scenario: If a colleague reports #NAME? on a formula that works for you, ask two questions before you look at the formula: what version, and what formula language. Between them they explain nearly every #NAME? that is not a typo.

9) TEXT() Reads Its Format Code in the Local Language

A cell's number format is stored in the file and is safe. The format code you pass to TEXT() is a string in a formula, and TEXT() reads it in the machine's language.

=TEXT($F2,"dd mmm yyyy")

In Manchester: 03 Aug 2026. In Munich, where the date codes are T for Tag, M for Monat and J for Jahr — and where lowercase m means minutes — that code is not describing the thing you think it is describing, and what comes back is not the date you asked for. Write "TT MMM JJJJ" and it works there and breaks in Manchester.

The fix is to name the language inside the code, which has worked since Excel 2013:

=TEXT($F2,"[$-en-GB]dd mmm yyyy")     → 03 Aug 2026 on every machine
=TEXT($F2,"[$-en-US]mmmm d, yyyy")    → August 3, 2026 on every machine

The locale tag also fixes month and day names, which is the other half of the problem: without it, "mmmm" on a Spanish machine returns agosto, which is correct behaviour and wrong output if the report goes to London.

🎯 Scenario: Any TEXT() whose result is joined into a label, a filename, a key or a lookup value should carry an explicit locale tag. Any TEXT() whose result a human reads on their own screen should not — that one you want localised. The distinction is whether the string will be compared with something, and strings that get compared must not be allowed to vary by desk.

10) Criteria Strings Are Re-Read on the Machine That Calculates

This is the one that survives being saved as .xlsx, and so the one that reaches furthest.

=SUMIFS($D$2:$D$21,$F$2:$F$21,">=01/08/2026",$F$2:$F$21,"<=31/08/2026")

The dates in that formula are not dates. They are characters inside a criteria string, and Excel converts them to dates when the formula calculates, using the settings of the machine doing the calculating. In Manchester the first bound is the 1st of August. In Chicago it is the 8th of January. The formula is stored once and means two things, and it changes its mind when the file crosses a desk rather than when anybody edits it.

The same applies to numbers in criteria. ">1.000" is a thousand in Munich and one-point-oh-oh-oh everywhere else.

Build the bound instead of typing it:

=SUMIFS($D$2:$D$21,$F$2:$F$21,">="&DATE(2026,8,1),$F$2:$F$21,"<="&EOMONTH(DATE(2026,8,1),0))

DATE takes three numbers and returns a serial. There is no text, so there is nothing to re-read. Better still, point the criteria at cells holding real dates and let the sheet show its own bounds:

=SUMIFS($D$2:$D$21,$F$2:$F$21,">="&$H$1,$F$2:$F$21,"<="&$H$2)

🎯 Scenario: Treat a date or a number typed inside quotation marks as a bug on sight, even when the answer is currently right. It is the only construct in this article that will follow a workbook into a locale-safe file format and quietly change its meaning years later, on a machine nobody thought about.

11) Never Let Excel Guess

Three tools, in order of how much you should prefer them.

NUMBERVALUE (Excel 2013+) takes the separators as arguments, which is exactly why it exists:

=NUMBERVALUE($C2,",",".")      → 12480.5 from "12.480,50", on any machine
=NUMBERVALUE($C2,".",",")      → for the other direction

VALUE cannot do this. VALUE reads the local settings, which makes it the function that caused the problem rather than the one that solves it.

DATE from parts, when the text is a known shape:

=DATE(RIGHT($F2,4)*1, MID($F2,4,2)*1, LEFT($F2,2)*1)     → a real 3 August 2026

With one trap that bites hard: this only works while the column is still text. If Excel has already converted it on open, LEFT($F2,2) reads the first two digits of the serial number — 46 — and you get a date in 2046 or an error, from a formula that was right yesterday. Which is why the parsing has to happen at import, before Excel has an opinion:

• Get Data → From Text/CSV lets you set the delimiter and the File Origin, and in the editor, Transform → Data Type → Using Locale… converts a column with the source's conventions rather than yours. This is the correct answer for anything recurring: it is stored in the query, it runs the same way on every desk, and it is the only fix in this article that survives the person who set it up leaving.
• In the legacy Text Import Wizard, the same job is the Advanced button on step 3 and the Column data format → Date: DMY dropdown.

ISO 8601 (2026-08-03) when you are the one writing the file. It is the only date text with no second reading anywhere: no regional setting puts the year first and then swaps the other two. If you export data that other people will parse, export dates this way and amounts with a plain . and no thousands separator at all, and the argument ends.

🎯 Scenario: The rule is the same for both halves of the problem. If a value's meaning depends on who is looking, pin it down with something that takes the ambiguity as an explicit argument — NUMBERVALUE with its separators, DATE with its three numbers, Power Query with its locale — and never with a function that reads the room.

12) Five Checks

Run these against any column that arrived from outside the building. The first two take a minute and would have caught everything in this article.

1. How many of these are actually numbers?

=COUNT($D$2:$D$21)&" of "&COUNTA($D$2:$D$21)     → "8 of 20"

Anything but n of n means part of the column is text, and every total over it is already wrong.

2. How many dates could be read two ways?

=SUMPRODUCT(--(DAY($F$2:$F$21)<=12))             → 10

A count of the rows that can move without erroring. Run it on the day the file arrives, not the day the reconciliation fails.

3. Do two checks of the same threshold agree?

=COUNTIF($D$2:$D$21,">10000")                    → 0
=SUMPRODUCT(--($D$2:$D$21>10000))                → 12

Equal is healthy. Different means mixed types, and the gap is roughly how much of the column is text.

4. Does the file's own total agree with yours?

The only check that catches the thousand-times-too-small rows, because those are valid numbers and no property of the cell betrays them. Ask the sender for a control total in the body of the email — not in the file — and compare. 577,574.80 against 1,526.95 is not a subtle difference.

5. Is anything suspiciously precise?

=SUMPRODUCT(--ISNUMBER($D$2:$D$21),--(ROUND($D$2:$D$21,2)<>$D$2:$D$21))     → 1

A currency column should have at most two decimals. 1.075 has three, and three decimals in a money column is a thousands separator that got eaten. One hit is enough to distrust the column.

13) Twelve Traps

1. A CSV holds text, not values. Every number and every date in it is re-decided by the machine that opens it, and the decision is not recorded anywhere.
2. A thousands point becomes a decimal point. 3.500 read in en-GB is 3.5 — a valid, unflagged, plausible number that is wrong by a factor of a thousand.
3. A number with both separators becomes text. 12.480,50 in en-GB is a string, and SUM, AVERAGE, MIN and MAX skip it in silence.
4. COUNTA cannot detect any of this — the cells are all full. Only COUNT beside it tells you anything.
5. In Excel, every text value is greater than every number. COUNTIF(">10000") skips text and SUMPRODUCT(--(range>10000)) counts it as a hit, so the same threshold gives two answers.
6. Dates with a day of 12 or lower swap in silence; days of 13 or higher never do. A half-broken date column is far harder to spot than a fully broken one.
7. .xlsx is locale-safe, .csv is not. Numbers, dates, formulas and format codes all travel intact inside a workbook.
8. Excel's "CSV (Comma delimited)" export writes the Windows list separator, so a comma-delimited export from a comma-decimal machine is semicolon-delimited.
9. Criteria strings are parsed at calculation time on the local machine. ">=01/08/2026" means two different days in two countries, and this one survives being saved as .xlsx.
10. TEXT() reads its format code in the local language. Tag it — "[$-en-GB]dd mmm yyyy" — whenever the result will be compared, joined or filed rather than simply read.
11. VALUE and DATEVALUE use local settings; NUMBERVALUE and DATE do not. Prefer the two that take the ambiguity as an argument.
12. Two date systems still exist. Workbooks created in older Excel for Mac count from 1904 rather than 1900, and copying dates between a 1904 workbook and a 1900 one shifts them by 1,462 days — four years and a day — with no error at all.

Nothing in this story is a mistake. Munich's file is correct German. Manchester's Excel is correctly configured for Manchester. Chicago's month-end formula is textbook. The ERP exported the conventions of the country it runs in, which is the only thing it could sensibly do.

What makes it expensive is that the cost of the disagreement is paid at the far end, by someone who cannot see it. Every other kind of broken data announces itself: a missing column is missing, a corrupt file will not open, a wrong formula returns #VALUE! in a colour. A locale mismatch returns a number. It returns a number that is the right shape, in the right column, in a file that opened cleanly, and the only evidence that anything happened is that somebody a thousand miles away is looking at a different one.

So the discipline is narrow and it is not about settings. Nobody should be reconfiguring Windows to make a workbook work; the settings are correct for the people using them. The discipline is that anywhere a string has to become a value, something in your file — NUMBERVALUE's two arguments, DATE's three numbers, a locale tag in a format code, a query step that names the source's conventions — has to say which reading is meant. Say it once, at the import, and the file means the same thing on every desk it lands on. Leave it unsaid and the file means whatever the desk means, which is another way of saying it does not mean anything at all.