Dates sort wrongly or change between computers: serial numbers, text dates and day-month order

A real date is a number

Excel stores a date as a serial number: the count of days from a start date. Microsoft documents two systems. The 1900 system counts from January 1, 1900, and the 1904 system from January 1, 1904. They differ by 1,462 days, which is four years and one day. Microsoft's example: July 5, 2011 is 40729 in the 1900 system and 39267 in the 1904 system.

When dates are pasted between workbooks that use different systems, they can arrive shifted by those 1,462 days. A tell-tale sign is a whole column of dates that is almost exactly four years out. Microsoft describes correcting this by adding or subtracting 1462 with Paste Special.

A text date is not a date

Entries that look like dates but are text align to the left, will not sort chronologically and cannot be used in date arithmetic. DATEVALUE converts text to a serial number, which then needs a date format applied. Its documentation notes that a missing year takes the current year from the computer's clock, that time information is ignored and that text outside the supported range returns an error.

When the text does not match what your system expects, Microsoft's #VALUE! page suggests building a real date from its parts, for example a DATE formula that picks the year, month and day out of a day-first text entry. Such a formula embeds a decision about order, so it is only safe for a column whose order you know.

• Left-aligned dates are text.
• A COUNT of the column lower than the number of filled cells means some entries are text.
• Convert on a copy, keep the original column and look at a sample afterwards.

Day-month order is interpreted, not stored in the text

The text 03/04/2026 does not say which number is the day. Microsoft's documentation shows Excel turning typed entries into dates automatically, such as 12/2 into 2-Dec, and says there is no way to turn this off; its workaround is to format cells as Text first or to type an apostrophe. Microsoft also says that when you type something Excel reads as a date, it formats it according to the default date setting in Control Panel, and that date formats beginning with an asterisk change when the regional configuration changes. The DATEVALUE page warns that the system date setting may change what it shows. The upshot is that a typed date depends on the computer it was typed on.

The result is that a column built from several people's typing can hold some entries read as day-first and some as month-first, both valid and both wrong for someone. When both numbers are 12 or less and differ, nothing in the cell can settle it.

Two-digit years and the cutoff

With two-digit years, Microsoft says Excel reads 00 to 29 as 2000 to 2029 and 30 to 99 as 1930 to 1999, and recommends four-digit years. A date typed as 31 is read as 1931, which is wrong for a list of this year's invoices. The cutoff is a rule that must be agreed for the file, not assumed.

Settling ambiguity, and the paid job

The honest approach is a written rule per source. For example: dates typed by the Leeds office are day-first, dates from the system export are year-month-day. Where a source is known, convert by its rule. Where it is not, list the row and let the owner decide. Output in the year-month-day form, which the W3C date-and-time note describes as an unambiguous representation, so the problem is not recreated.

The clean-up job, from £125 with the final price confirmed after we see the column list, covers one file of up to 20,000 rows with up to three date columns and two number columns (an untested proposal). It is accepted by the conversion counts, a reference set of at least thirty dates you confirm from source documents and a list of every ambiguous row, with payment after your sign-off. It does not guess dates, handle time zones or decide which dates are right for your business. Send the column list and what you know about each source, not the file.