The date problems that silently corrupt models.

1. The DD/MM versus MM/DD ambiguity

The same string — 03/04/2025 — is parsed as 3 April on a European locale and as 4 March on a US locale. When a CSV is imported into a workbook whose Windows region differs from the source, Excel applies the active locale's date template silently. Days 1–12 are accepted by either interpretation, so the mistake only becomes visible on day 13 of any month.

Detection heuristic

After importing a date column, run =SUMPRODUCT(--(DAY(A:A)>12)). Compare against the same formula on MONTH. If MONTH values are clustered at 1–12 (as they must be) but DAY never exceeds 12, your dates may be transposed.

Fix

Re-import via Data → From Text/CSV and specify the source locale explicitly in the wizard. Do not rely on Excel's auto-detect for date columns crossing locales.

2. The 1900 versus 1904 epoch

Excel for Windows defaults to the 1900 date system (serial 1 = 1 January 1900) while Excel for Mac historically defaulted to the 1904 system (serial 1 = 2 January 1904). A workbook saved under one system and opened under the other shifts every date by approximately 1,462 days — roughly four years.

The setting is found at File → Options → Advanced → Use 1904 date system. When workbooks created under different settings are linked, Excel does not reconcile the difference automatically.

Detection

Type =DATEVALUE("1900-01-01") in any cell. The result is 1 under the 1900 system and -1462 (rendered as ####### until formatted) under the 1904 system.

3. The 1900 leap-year bug

For backwards compatibility with the original Lotus 1-2-3, Excel treats 1900 as a leap year and recognises 29 February 1900 as a valid date. It is not. Any difference between two dates that crosses 1 March 1900 includes a phantom day. In modern finance work this never occurs in practice, but it does break interoperability with libraries that implement the calendar correctly — Apache POI, openpyxl in strict mode, Power Query in some configurations.

4. Two-digit-year interpretation

When a CSV contains 05/06/24, Excel applies a two-digit year cutoff (default: 1930–2029) and produces 2024. The cutoff is configurable in Windows regional settings, and different machines in your organisation may apply different cutoffs. This is the most under-recognised cause of off-by-a-century date drift in consolidations that pull from multiple sources.

Mitigation

Reject two-digit years at the import boundary. Coerce with =DATE(2000+RIGHT(A1,2), MONTH(...), DAY(...)) only if you are certain the source year is in the 2000s; otherwise demand four-digit years from the upstream system.

5. Dates stored as text

The most common diagnosis when SUMIFS with a date criterion silently returns zero: the date column is text. Visually identical, but Excel does not coerce text to date inside SUMIFS's criterion comparison.

Detection

A genuine date right-aligns by default. A date stored as text left-aligns. Or use =ISTEXT(A2) on the column.

Fix

Select the column → Data → Text to Columns → click through to step 3 → Date → pick the source format (DMY, MDY, YMD). This is the only built-in Excel command that lets you name the source format unambiguously.

6. Time-zone drift in Power Query

Power Query reads CSV timestamps as DateTime.Type in the local time zone unless you explicitly cast to DateTimeZone.Type. Workbooks shared across time zones can therefore show different month-end totals depending on who refreshes the query. Always promote to DateTimeZone at the source step and convert to UTC before any aggregation.

7. NETWORKDAYS with the wrong holiday list

The NETWORKDAYS family takes an optional holidays argument. A model authored for a US team and re-used by a German subsidiary will count US federal holidays as working days and ignore German Bank holidays unless the holiday range is replaced. Keep holiday lists per legal entity, not per workbook.

8. Locale-aware TEXT() formats

The format string passed to TEXT() is locale-dependent. TEXT(A1, "mmmm") returns "January" in English Excel and "Januar" in German Excel. If your model emits formatted dates for downstream concatenation, wrap with TEXT(A1, "[$-409]mmmm") to force English regardless of the user locale, or [$-407] for German.