The Date Trap

A date is a number wearing a costume

Excel stores a date as the count of days since 1 January 1900, which is day 1. 15 March 2026 is the number 46096; a time is the fraction of a day after the decimal point, so 18:00 is 0.75. This is why you can subtract two dates and get a number of days, and why a date column that has lost its formatting suddenly shows five-digit numbers. Nothing is broken there — only the mask is gone, and a number format brings it back.

Three failure modes, in order of frequency

• Text that looks like a date. Imported 05.03.2026 that Excel never parsed. It sits left-aligned, sorts alphabetically (so December lands between April and February), and every date function returns an error on it.
• The day/month swap. 03/05/2026 is 3 May in Europe and 5 March in the United States. Excel resolves it using the system locale, not the file, so the same CSV opened on two machines produces two different datasets — and only the dates from the 13th onward are unambiguous enough to fail loudly.
• Two-digit years. Excel reads 00-29 as 2000-2029 and 30-99 as 1930-1999. A birth date of 15.06.30 becomes 1930, which may be right, or 2030, which is not.

The test

The same one as for numbers, because dates are numbers: =ISNUMBER(A2), or =COUNT(A2:A200) against =COUNTA(A2:A200) over the date column. A real date is counted by COUNT. Text is not. There is no third possibility, and no amount of formatting changes the answer.

=ISNUMBER(A2)                      ' TRUE for a real date
=A2+0                              ' shows the serial number
=DATEVALUE("05.03.2026")           ' parses text using the system locale
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))   ' parses dd.mm.yyyy explicitly

The last line is the one to reach for when the locale is against you: it takes the pieces by position and builds the date yourself, so the result does not depend on which machine opens the file. For a recurring import, Power Query's Change Type With Locale does the same thing once and permanently.

Why it matters for statistics

Anything time-ordered depends on the date being a number: sorting, filtering by period, grouping by month in a PivotTable, differences in days, moving averages, trend and seasonality in the later time series courses. Text dates sort into nonsense, and a sorted-but-wrong series produces a trend line that is confidently wrong. Fix dates before the first chart, not after.

No lab in this lesson

This course's lab engine has no date functions, and a lab that pretended to test dates with SUM would teach you nothing. Do this one in Excel: type =TODAY() in a blank cell, then format it as a number to see the serial, and type 05.03.2026 and 05/03/2026 in two cells and run ISNUMBER over each.