Days between, month buckets, and the epoch trap

A date is a number wearing a costume

Lesson 2.5 established that a spreadsheet stores a date as a serial number — days since the epoch — and shows it formatted. R does the same with the Date class: as.numeric(as.Date("2025-01-06")) is 20094, days since 1970-01-01. Two consequences follow. Subtracting two real dates gives days, in both tools. And a date typed as text — "06/01/2025" in quotes, or a cell Sheets could not parse — is not a date at all, and arithmetic on it fails or, worse, quietly lies.

Worked example — days to quarter end

Maple & Birch wants to know how many days each order sat inside the quarter that ended 2025-03-31. With order_date in column F:

=DATE(2025,3,31)-F2

DATE(year, month, day) builds a real date from three numbers; subtracting F2 gives 84 for order 1001 (6 January to 31 March). Filled down: 84 · 82 · 75 · 69 · 56 · 49 · 45 · 32 · 27 · 20 · 12 · 6. If the result shows as a date such as 1900-03-24, the cell inherited a date format from the subtraction — set it to Number. In R:

> as.numeric(as.Date("2025-03-31") - as.Date(orders$order_date))
 [1] 84 82 75 69 56 49 45 32 27 20 12  6

as.Date turns the text column into real dates (the CSV delivered it as text). The subtraction returns a difftime — printed as Time difference of 84 days for a single pair — and as.numeric strips it to plain numbers. Adding a number to a date works too: as.Date("2025-01-06") + 30 is "2025-02-05".

The month bucket

A pivot by month (Module 7) needs a column with one value per month. Neither the serial number nor the full date will do — every order date is distinct. The standard derived column is a year-month text:

=TEXT(F2, "yyyy-mm")

gives "2025-01", and sorts correctly because the year comes first. R's format does the same job with the C-style codes %Y (four-digit year) and %m (two-digit month):

> format(as.Date(orders$order_date), "%Y-%m")
 [1] "2025-01" "2025-01" "2025-01" "2025-01" "2025-02" "2025-02" "2025-02"
 [8] "2025-02" "2025-03" "2025-03" "2025-03" "2025-03"

%d is the day, %b the short month name: format(as.Date("2025-01-06"), "%d %b %Y") is "06 Jan 2025".

The trap: a date that parses wrong without complaint

as.Date accepts "2025-01-06" and "2025/01/06" on its own. Hand it a Canadian day-first string and it does not stop:

> as.Date("06/01/2025")
[1] "6-01-20"
> as.Date("06/01/2025", format = "%d/%m/%Y")
[1] "2025-01-06"

The first call read 06 as the year, 01 as the month and the first two digits of 2025 as the day — a real Date object, year 6, with no warning. The second names the layout with format and gets 6 January 2025. Whenever the source is not ISO yyyy-mm-dd, state the format. Sheets has the mirror-image problem: a spreadsheet in a Canadian locale may read 06/01/2025 as 6 January while a US-locale sheet reads it as 1 June; DATEVALUE and the locale setting decide, and a column that is half text and half dates sorts into two blocks.

Type both, then count the days

Type the TEXT bucket and the R format. The numeric item asks for the days from order 1001 to quarter end.

CRISP-DM: this is data preparation → format data when the fix is the type, and construct data when the result is a new bucket column.