Incorrect data types and inconsistent dates

In depth

Numbers stored as text

Numbers end up as text because of what is written around or inside them. Each pattern needs its own rule.

The decimal mark and the thousands separator depend on the writer’s locale: the conventions for a language and region, covering decimal and grouping marks, date order, currency and more. Software stores these conventions as locale data. Formatted for the United States or the United Kingdom, one thousand two hundred and fifty is 1,250.00; for Germany, 1.250,00; for France, 1 250,00, where the gap is a narrow non-breaking space. The same characters mean different numbers in different locales: “1,250” is one thousand two hundred and fifty to an American reader and one and a quarter to a German one. Nothing in the value tells you which; only its source does.

So convert numbers source by source. Find out which locale each source used, then for each value: remove the currency symbol (recording the currency in its own column if there is more than one), remove the grouping separator, replace the decimal mark with a point, turn parentheses and Unicode minus signs into an ordinary minus, and parse. Parsers are strict about that last detail. Python’s float(), for example, rejects “−45” written with the Unicode minus. Decide once whether a percentage column holds fractions (0.12) or percentage points (12) and say so in the data dictionary.

Then validate. Count the values that failed to convert and look at every one of them. Range-check the ones that succeeded: a coffee price of $1,800.00 converts perfectly and is almost certainly a typing error for $18.00. Conversion makes values comparable; it doesn’t make them right.

Identifiers that look like numbers

Postcodes, account numbers, phone numbers, product codes and student numbers are made of digits, but they are labels, not amounts. Nobody adds two postcodes or averages two phone numbers. Store them as text. Stored as numbers they lose information: a code such as 004517 loses its leading zeros and becomes 4517, and a 16-digit card or account number loses its last digit, because Excel keeps at most 15 significant digits and turns the rest into zeros. A useful test is to ask whether arithmetic on the column would ever make sense; if not, it is text, however numeric it looks.

Lost leading zeros can be restored only when you know the code’s exact width. If the data dictionary says every site number has six digits, padding 4517 with zeros to 004517 is safe. If codes come in several lengths, padding invents codes; go back to the source and import the column as text. Digits lost beyond the 15th cannot be recovered at all.

Dates in different orders

People write the parts of a date in different orders: day/month/year in most of the world, month/day/year as the usual order in the United States, and year-month-day in ISO 8601 and in several East Asian conventions. A value such as 25/03/2026 can be read only one way, because there is no month 25. A value such as 04/03/2026 can be read as 4 March or as 3 April. An ambiguous date is one whose day and month could be swapped to give a different valid date.

Ambiguity is common. A date is ambiguous when its day number is 12 or less and differs from its month number. In a year of 365 days, 144 dates have a day number of 12 or less; 12 of them (1 January, 2 February and so on to 12 December) read the same either way, which leaves $144 - 12 = 132$ genuinely ambiguous dates, about $132 \div 365 \approx 36\%$ of the year. More than a third of slash-dates can’t be read without outside information.

Resolve them with evidence, in this order:

1. The source’s convention. Documentation, export settings or the system’s locale often state the format outright.
2. Other values from the same source. If a source writes 14/03/2026 anywhere, it puts the day first. This works only if the source is one system or one person using one convention throughout; check that no value from the source contradicts it.
3. Business rules. An order can’t be placed after it was dispatched, a check-in can’t follow its check-out, and every date should fall within the export period. These cross-field checks and range checks often rule out one reading.
4. Ask, or flag. If both readings survive, ask the person or team who produced the value. If nobody can say, flag the value as unresolved and record the decision. Never guess silently.

Two-digit years need a century rule too. Python’s %y, for example, reads 00–68 as 2000–2068 and 69–99 as 1969–1999. Written-out dates bring their own problems: month names depend on language (“4 mars 2026” is French), and some, such as “Mar 4”, have no year at all.

ISO 8601

The international standard for writing dates and times is ISO 8601. Its usual date format is YYYY-MM-DD, as in 2026-03-04 (the standard calls this the extended format; its basic format drops the hyphens, 20260304), with the units running from largest to smallest: year, month, day, then hour, minute, second. That order makes the format unambiguous and means that dates written this way sort correctly even as text. The current edition is published in two parts, ISO 8601-1:2019 (basic rules) and ISO 8601-2:2019 (extensions). A date and time is written with a T between them, and a time can carry its relation to UTC: 2026-03-14T23:30:00Z means 23:30 in UTC (the Z stands for an offset of zero), and 2026-03-14T18:30:00-05:00 is the same instant written as a local time five hours behind UTC. RFC 3339, the profile of ISO 8601 used in internet protocols, defines the offset as local time minus UTC.

Spreadsheet serial dates

Spreadsheets store a date as a serial date: a count of days from a starting point, displayed through a date format. Change the cell’s format to General and 4 March 2026 appears as 46085. That is why serial numbers turn up in exports: somewhere, a date cell lost its format or was saved as a number.

Excel has two systems. In the 1900 date system, the default in Excel for Windows and in current Excel for Mac, 1 January 1900 is serial number 1. When Lotus 1-2-3 was first released it treated 1900 as a leap year, which it was not, and Excel kept the same assumption so that its serial numbers matched Lotus’s. Excel therefore counts a 29 February 1900 that never happened (serial 60). Microsoft judged that correcting it would shift almost every existing date by a day and break compatibility, and the only visible symptom it lists is that the WEEKDAY function is wrong for dates before 1 March 1900. For any date from 1 March 1900 onwards, the serial number is simply the number of days since 30 December 1899:

The 1904 date system, the default in earlier versions of Excel for Mac, counts from 1 January 1904 as day 0. The two systems differ by 1,462 days, four years and one leap day: Microsoft’s own example gives 5 July 2011 as 40729 in the 1900 system and 39267 in the 1904 system. If a workbook used the 1904 system and its serial numbers are read as 1900-system numbers, every date comes out four years and a day early.

You can recognise serial numbers by their size: in the 1900 system, 1 January 2025 is 45658 and 31 December 2026 is 46387, so five-digit integers between those values in a date column are almost certainly dates in 2025 or 2026. Convert them with the origin stated, for example pd.to_datetime(s, unit='D', origin='1899-12-30') in pandas, and check a few results against the source.

Times and time zones

A timestamp without a time zone is ambiguous in a different way. Lantern’s shop records order times in UTC. Suppose Lantern’s local time is five hours behind UTC. An order placed at 23:30 local time on 14 March is stamped 2026-03-15T04:30:00Z, so a report that groups orders by the UTC date moves it into 15 March. Neither date is wrong; they answer different questions. Store timestamps in UTC, or as local time with its offset, and choose the time zone of every daily, weekly or monthly report deliberately, usually the business’s own local day.

Never store local clock times without an offset. Where clocks change for daylight saving, an hour of local time is skipped each spring and an hour is repeated each autumn, so a local time in the repeated hour could be either of two instants, and durations that span a change come out an hour wrong. An offset, or a stored time in UTC, removes both problems.

Booleans written many ways

A yes/no column, such as Lantern’s subscription, can arrive as Y/N, yes/no, TRUE/FALSE, 1/0, “subscribed” or a tick. Convert it with an explicit list of accepted spellings for true and for false, and flag anything else. Treat a blank as missing, not as false: a blank means nobody recorded the answer, which is a different fact from “no”, and a later lesson in this course deals with missing values properly.

Convert with an explicit format, then validate

1. Profile the raw column: list the distinct patterns (for example, the share of values that look like 99/99/9999, 9/9/99 or five-digit integers) and which source each comes from.
2. Decide the target type and, for each source, the exact format it used.
3. Convert each pattern with its explicit format, keeping the raw column untouched beside the converted one.
4. Count the values that failed and look at each one; fix the rule or flag the value.
5. Validate the result with range checks and cross-field checks, and record any value you resolved by judgement.

The tools differ only in syntax. In a spreadsheet, import identifier columns as Text (Microsoft recommends doing this on import, for example with Power Query) and rebuild dates from their parts with DATE, so that the order of day and month is your decision. In SQL, PostgreSQL’s to_date(order_date, 'DD/MM/YYYY') and to_number take an explicit format. In pandas, pd.to_datetime(s, format='%d/%m/%Y', errors='coerce') and pd.to_numeric(s, errors='coerce') turn values that don’t fit into missing values you can count, rather than stopping at the first one.