Three types that misbehave

Dates are numbers wearing a format

Type 2025-01-06 into a Sheets or Excel cell and the cell stores 45663 — the number of days since the tool's day zero — and displays it as a date. Change the cell format to Number and the 45663 appears; the value never changed. This is why date arithmetic works (=F3-F2 gives 2, the days between orders 1001 and 1002) and why the month is one function away: =MONTH(F2) returns 1. R does the same with a different origin: as.Date("2025-01-06") is stored as 20094, days since 1970-01-01, and format(as.Date(orders$order_date), "%Y-%m") gives a month label per row:

 [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"

The failure mode is a date typed as text — "06/01/2025" in a text cell. It is not 45663 underneath; it is a string. It will not subtract, MONTH returns an error, and it sorts alphabetically: in R, sort(c("4/3/2025", "15/1/2025", "3/2/2025")) returns "15/1/2025" "3/2/2025" "4/3/2025", because "1" comes before "3" comes before "4". Pasting the serial between tools is the other trap: 45663 means 2025-01-06 in Excel and in Sheets but a date in the year 2095 in R.

Numbers stored as text

An export sometimes delivers amount as text. The symptom is visible before any formula: the values sit left-aligned in the cell (numbers align right), and Excel shows a small green triangle. The formula symptom is worse because it is silent: =SUM(D2:D13) returns 0 with no error, since text contributes nothing to a sum. The fix is =VALUE(D2) filled down, or in R as.numeric(x): sum(as.numeric(c("420", "180", "760"))) gives 1360 where class(x) was "character". If a whole column reads as chr in R's str(), one bad cell — a stray letter, a comma in "1,320" — turned the entire column into text.

Logicals count themselves

A comparison produces a logical: TRUE or FALSE. In R, orders$amount > 500 is a vector of twelve of them:

 [1] FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE

The idiom to own: TRUE counts as 1, so sum(orders$amount > 500) is the count of orders over $500 — [1] 5 — and mean(orders$amount > 500) is the proportion — [1] 0.4166667. The Sheets equivalent is =COUNTIF(D2:D13, ">500") → 5, and =COUNTIF(D2:D13, ">500") / COUNT(D2:D13) for the proportion. sum() of a logical is a count, mean() of a logical is a proportion: two lines that replace a dozen filters.

Type the four calls, then compute the proportion

The snippets below are the four one-liners: the month in Sheets and in R, the text-to-number fix in Sheets, and the logical count in R. The code block then does the count and the proportion in plain Python on the shared amount list — 5 orders, 0.4167 — the same numbers the R idiom and the COUNTIF return.

CRISP-DM: recognising a serial, a text-number or a logical is data understanding → verify data quality; converting it is data preparation → clean (VALUE, as.numeric) or construct (a month column, a TRUE/FALSE flag).