My dates imported as five-digit numbers

A column that should hold dates contains numbers around 45000 instead. They are all in a narrow range, and sorting them still puts the rows in the right order.

Why this happens

  • Spreadsheets do not store dates as text. They store a count of days from a fixed starting point and remember, separately, that the column should be displayed as a date. An export that carries the value without the display format hands you the count.
  • That the sort order still works is the giveaway. The numbers are genuinely the dates, in a form that has lost the instruction on how to read it.
  • Converting requires knowing which starting point was used, and there is more than one. The common one counts from the end of 1899, the Macintosh convention historically counted from 1904, and the common one deliberately includes a day that never existed, 29 February 1900, to stay compatible with a bug in a spreadsheet from the 1980s. A conversion that ignores that is a day out for early dates.
  • So a five-digit number is not self-describing. Nothing in the value says which epoch produced it, which is why guessing is a real risk rather than a theoretical one.

What to do right now

  • Format the column as a date in the tool that produced the file. That is the one place the correct starting point is already known, and it turns the numbers back into dates with no arithmetic.
  • If you have to convert outside that tool, confirm which system the file came from first, then check a row whose real date you already know before converting the rest.
  • Ask for the export again with the date column formatted, or better, as ISO text. A date exported as 2026-08-04 carries its own meaning and needs no epoch at all.
  • Be careful with any date before March 1900 if you converted by hand. That is where the deliberate off-by-one bug lives.

Stopping it on the next file

A date field will not accept a bare 45678, and refuses it by name rather than converting it. That is deliberate rather than a gap: turning a five-digit number into a date means choosing an epoch the file never states, and a silently wrong date is the exact failure this product exists to prevent. What it does accept is every form that carries its own meaning, including 2026-08-04, 04/08/2026 read against the whole column's convention, and “4 Aug 2026”.

The invoice line items template is a sheet with those rules already set, if you want to see the shape of one before building your own.

Other things that go wrong

My file has dates that do not existA date column contains days that are not on the calendar: a 31st in a thirty-day month, or a 29 February in a year that has no such day. Nothing flagged them, and they exported the same way they arrived.(1,234.56) is not being read as a negative numberAmounts wrapped in parentheses either fail to import or come through as positive numbers. A column that should net out to a small figure is roughly twice what it should be.The same email address is in my list twiceA contact list contains what is obviously the same person more than once. The addresses look identical until you notice the capitalisation, or a stray space, or that one of them is wrapped in a display name.My yes/no column has five different spellingsA column that only ever means yes or no contains several spellings of each. Filtering for “Yes” misses rows, and a count of the yeses is lower than it should be.

Define the sheet once

Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.

Start freeAll fixes