← Back to Insights

Definitional

12 min read

Is 03/04 the 3rd of April or the 4th of March? The quiet data bugs in your automations

By Ben Gould · Published 19 November 2026

Data Quality
Workflow Automation
Process Automation
Reliability
Integration

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.

Is 03/04/2026 the 3rd of April or the 4th of March?

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:

  • 03/04/2026 is read as 4 March. Wrong, and nothing complains.
  • 13/04/2026 can't be a date in the American order, because there's no thirteenth month. Depending on the tool it's left as plain text, rejected, or blanked.
  • 05/05/2026 is right by coincidence.

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.

What is ISO 8601, and why does YYYY-MM-DD fix it?

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.

Nobody wants to read 2026-04-03. Where does the friendly version go?

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 storedWho is readingWhat they see
2026-04-03UK customer (en-GB)3 April 2026
2026-04-03US customer (en-US)April 3, 2026
2026-04-03German customer (de-DE)3. April 2026
2026-04-03The next system2026-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.

What changes when there's a time attached?

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.

Why is "1" not the same as 1?

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 should a number be stored as text?

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:

  • Leading zeros vanish. 07700 900123 becomes 7700900123, which is no longer a phone number, and a sort code beginning 04 loses its zero the same way.
  • Long ones get rounded. Excel keeps 15 significant digits, and in Microsoft's own words any digits past the 15th "are rounded down to zero" (Microsoft). A 16-digit card number or an 18-digit record ID comes back subtly different, and it now points at a different record or at none.
  • Some turn into dates. This one became famous in genetics. A 2016 study in Genome Biology found that about a fifth of papers with gene lists in Excel contained names the spreadsheet had converted, with the gene MARCH1 becoming 1-Mar and SEPT2 becoming 2-Sep (Ziemann et al.). In 2020 the committee that names human genes gave up and renamed them, so MARCH1 is now MARCHF1 (HGNC guidelines).

Why does 0.1 + 0.2 come out as 0.30000000000000004?

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.

So how should an automation handle money?

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.

What to ask whoever builds it

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 dataThe questionThe 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 bottom line

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.

Related reading

Or see how I put this into practice: services, case studies.