← Back to Insights
12 min read
By Ben Gould · Published 19 November 2026
Most automations that go quietly wrong aren't broken in their logic. They're wrong about what a value is. One rule prevents nearly all of it: store and pass every value in one unambiguous form, and only make it friendly at the point a person reads it. Dates as YYYY-MM-DD, numbers as numbers, IDs as text, money in pence.
An automation is a chain of systems handing values to each other: a form to a spreadsheet, a spreadsheet to a CRM, a CRM to an accounting package. Every hand-off carries an assumption about what the value is, and the systems don't always agree. When they disagree, nothing usually errors. The run goes green, the record is created, and a delivery date is a month out or an invoice has skipped an approval.
Both, depending on who reads it. In the UK and most of the world it's 3 April. In the US it's 4 March. A person can usually tell from context. Software can't, so it applies whatever its settings say, and those settings were often chosen by someone else: the default region of the account, the server the tool runs on, the locale of the spreadsheet a colleague created.
What makes this dangerous is that it's only wrong some of the time. Hand a list of UK dates to a tool that expects American ones and you get three different outcomes in the same column:
So testing with today's date tells you very little. A workflow built and tested on the 20th of the month works perfectly, because the 20th can only be read one way. It starts corrupting records on the 1st of the next month and stops again on the 13th. Any date where the day is 12 or under can be read both ways, and that's 144 days of the year.
ISO 8601 is the international standard for writing dates and times, first published in 1988 (ISO). The part worth knowing is one line long: write the year, then the month, then the day, biggest unit first. The 3rd of April 2026 is 2026-04-03.
It solves the problem for two reasons.
It can only be read one way. Nobody, anywhere, writes year-day-month. A value that starts with a four-digit year is understood identically by a person in Leeds, a person in Chicago and every system they use.
It sorts correctly as plain text. Sort a column of DD/MM/YYYY dates alphabetically and you get every 1st of the month together, then every 2nd. Sort YYYY-MM-DD the same way and it comes out in date order, because the biggest unit is on the left, exactly as it is in an ordinary number. This is also why files named 2026-04-03-board-pack line up properly in a folder.
So the rule for an automation is that every date travelling between systems travels as YYYY-MM-DD.
At the end, and only at the end.
You already know this idea from spreadsheets. A cell holds one value, and "Format cells" changes how that value looks without changing what it is. Behind the cell showing "3 April 2026", Excel is holding the number 46115, a count of days. You can show it as 03/04/2026, Fri 3 Apr or April 3, 2026 and the value underneath never moves.
Engineers call the place where that happens the presentation layer: the email, the PDF, the dashboard, the message in Slack. It's the last step before a human being, and it's the only place a date should take a local shape. Every tool worth using can format a stored date for a given locale, which is just a code for a language and a country:
| What is stored | Who is reading | What they see |
|---|---|---|
| 2026-04-03 | UK customer (en-GB) | 3 April 2026 |
| 2026-04-03 | US customer (en-US) | April 3, 2026 |
| 2026-04-03 | German customer (de-DE) | 3. April 2026 |
| 2026-04-03 | The next system | 2026-04-03 |
One stored value, four correct displays, and no possibility of the systems in the middle disagreeing. If people need to read a date and there's any chance of an overseas reader, spell the month out. "3 April 2026" has never been misread by anyone.
The mistake to avoid is formatting early. Once a step in the middle of a workflow turns a date into "03/04/26" because it looks nicer in the spreadsheet, every step after it is back to guessing.
The same applies when an AI step is doing the reading. A model pulling a due date off a supplier's invoice faces the same ambiguity you do, so tell it the format to return, and check what comes back with an ordinary rule: is it a real date, is it after the invoice date, is it within the year. That's the "constrain the shape, then check the content" control from the accuracy post, applied to one field.
One more thing can move: the clock. The UK spends about seven months of the year an hour ahead of UTC, the world's reference time, and five months level with it. An automation that stores "midnight on 3 April" in UK time and passes it to a system that thinks in UTC has handed over 11pm on 2 April. If that system then keeps only the date, the record is a day early. It works all winter and breaks when the clocks go forward, which is why this one tends to be discovered in April.
The answer has the same shape as before. Store times in UTC, or with their offset written down, and convert to the reader's local time on the way out. If a value is really just a date (a birthday, a due date, a contract start), keep it as a date and don't let a tool attach a time to it.
Because one is text and the other is a number, and software treats those as different kinds of thing even when they look identical on screen.
If you've ever had a VLOOKUP fail on a value you could plainly see in both sheets, and noticed the small green triangle offering "number stored as text", you've met this already. To you, the two cells say 1042. To the spreadsheet, one of them is a quantity and the other is four characters that happen to be digits, and it won't match them.
Automations hit this constantly, because types get lost in transit. A CSV file has no types at all: everything in it is text, and each tool that opens it guesses. One system sends an order number as a number, the next expects it as text. Three things then go wrong, quietly.
Matching fails. A step that looks up customer "1042" in a list where it's stored as 1042 finds nothing. Depending on how it was built, it then stops, or creates a duplicate customer, which is worse.
Maths turn into gluing. Add 1 and 1 and you get 2. Add "1" and "1" and many tools give you "11", because joining is what adding means for text.
Comparisons go alphabetical. Text is compared one character at a time from the left, the way a dictionary orders words. So "10" comes before "9", and "10000" is less than "5000", because 1 comes before 5.
Most workflow tools ask, somewhere in a dropdown, whether a comparison is a text one or a number one. It's the most consequential small choice in the build, and it's usually left on whatever the default was.
When it isn't a quantity. The test is simple: would you ever add it up or average it? If not, it's a label that happens to be made of digits, and it should be text from start to finish.
Phone numbers, order numbers, account numbers, bank sort codes, employee IDs and tracking references are all labels. Treat them as numbers and the software "helpfully" tidies them:
Because computers store most decimals as very close approximations, and now and then the approximation shows.
The reason is easier than it sounds. Try writing one third as a decimal: 0.333, and however many threes you add, you never quite get there. Our number system can't write a third exactly. Computers count in binary, and binary has the same trouble with a different set of fractions, including one tenth. So 0.1 is stored as the nearest value the machine can hold, which is out by a few billionths of a billionth. Add two of those together and the tiny errors surface as 0.30000000000000004.
This isn't a fault in one tool. It's the standard way nearly every computer, spreadsheet and programming language has handled decimals since the 1980s, and Python's own documentation is blunt about it: "this is not a bug in Python, and it is not a bug in your code either" (Python docs). It happens every time. You only see it sometimes, because most screens round to a couple of decimal places before showing you anything.
An error that small matters in two places.
Checking whether two amounts are equal. Three line items of 10p should add up to 30p. Ask a computer whether 0.1 + 0.1 + 0.1 equals 0.3 and the answer is no. A reconciliation step that checks "do the lines match the invoice total?" will flag invoices that are perfectly fine, and someone will spend a morning looking for a penny that doesn't exist.
Converting and rounding. £19.99 multiplied by 100 should be 1,999 pence. On most systems it comes out as 1998.9999999999998. If the next step drops the decimals instead of rounding, that's 1,998 pence. A penny per line doesn't sound like much until it's two hundred lines a day and the ledger no longer agrees with the bank.
In pence, as whole numbers. Whole numbers are stored exactly, so £19.99 becomes 1999 and stays 1999 through every sum, with the pound sign and the decimal point added back at the presentation layer, like the date.
This is how the payments industry already works. Stripe's API expects every amount in the currency's smallest unit, so you send 1099 to charge 10.99 (Stripe). Where a calculation genuinely produces fractions of a penny, such as VAT or a percentage discount, decide once where the rounding happens and which way it goes, and do it in exactly one place. HMRC, your accounting package and your customer all need to arrive at the same number.
None of this needs you to write a formula. It needs four questions, and the quality of the answer tells you most of what you need to know.
| The data | The question | The answer you don't want |
|---|---|---|
| Dates | "What format do dates travel in between systems?" | "Whatever the spreadsheet shows." |
| Times | "What happens to this the week the clocks change?" | "It should be fine." |
| IDs and codes | "Are order numbers and phone numbers text all the way through?" | "They're numbers, aren't they?" |
| Money | "Is money held in pence, and where exactly does rounding happen?" | "It rounds at the end somewhere." |
Then one test that costs ten minutes. Run the workflow once with awkward values on purpose: a date of 03/04, a phone number starting with 0, an ID sixteen digits long, and an amount of £19.99. If all four arrive at the other end intact, whoever built it has thought about this. If you'd like to know what should happen when they don't arrive intact, that's validation, and it's covered in how to keep AI automations reliable.
The expensive data bugs aren't the ones that crash. A crash gets noticed and fixed the same day. The expensive ones produce a plausible value: a date that's a real date, just the wrong one, or an approval rule that runs without complaint and compares the wrong thing.
The defence is dull and it's the same in every case. Decide what each value is, keep it in one unambiguous form for the whole journey, and leave the friendly formatting to the last step before a person reads it. It costs almost nothing when it's done on the day the automation is built, and a good deal more once a year of records has to be untangled.
If you have a workflow passing dates, IDs or money between systems and you aren't sure which of these it gets right, a thirty-minute discovery call is usually enough to find out. See process automation and n8n consulting for how I approach the work.
Built in an afternoon, broken by Christmas: what makes an automation last
Read more →AI agents vs simple automation: which does your process actually need?
Read more →How to keep AI automations reliable: monitoring, fallbacks and human-in-the-loop
Read more →Or see how I put this into practice: services, case studies.