Date Functions

How many days passed between the opening and the closing of a fault record looks like a simple subtraction. It is not: subtraction gives the interval between the two endpoints, while the question being asked is usually the number of days covered, and the two differ by one day. This lesson establishes what a date is in a cell, computes the mean resolution time of the same request set under three decisions, and shows that the phrase “one month later” yields two different dates at month end.

• BO16. A date in a cell is a number: the count of days from a starting day; in this model serial 1 is January 1, 2026. The format is only an appearance.
• BO17. The support requests and reading days are fictional. The reading day is distributed between the 1st and the 31st of the month; this is what creates the month-end question.
• BO18. A weekend is two days, and no holidays are defined. workdays counts the weekdays with both endpoints included; if a holiday list is added, the count changes again.

What Sits in the Cell Is a Number

The block below builds the calendar conversion, the reading days, and the support requests.

// calendar.mjs — spreadsheet MODEL: a date in a cell is a NUMBER, the format is only an appearance.
// Serial number 1 = January 1, 2026. The support requests and reading days are fictional.
export const SEED = 20260602, REQUESTS = 54, ROWS = 60;
const rng = (t) => { let s = t >>> 0;
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };

// The calendar conversion covers 2026-2027; neither year is a leap year, both are 365 days.
const MONTHS = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
const BEFORE = MONTHS.map((_, i) => MONTHS.slice(0, i).reduce((a, b) => a + b, 0));
export const serial = (y, m, d) => (y - 2026) * 365 + BEFORE[m - 1] + d;   // serial 1 = January 1, 2026
export const toDate = (s) => { let k = s - 1, y = 2026, m = 0;
  while (k >= 365) { k -= 365; y++; }               // a day that crosses the month boundary spills into the next month
  while (k >= MONTHS[m]) k -= MONTHS[m++];
  return [y, m + 1, k + 1]; };

const REF = /(\$?)([A-Z])(\$?)(\d+)/g;
export const shift = (f, dC, dR) => f.replace(REF, (_, a, s, b, r) =>
  (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dC)) + (b ? "$" + r : String(+r + dR)));

const two = (n) => String(n).padStart(2, "0");
const FUNCS = {
  date: (y, m, d) => serial(y, m, d),
  month: (s) => toDate(s)[1], day: (s) => toDate(s)[2],
  format: (s) => { const [y, m, d] = toDate(s); return `${two(d)}.${two(m)}.${y}`; },
  monthEnd: (s) => { const [y, m] = toDate(s); return serial(m === 12 ? y + 1 : y,
    m === 12 ? 1 : m + 1, 1) - 1; },                 // one before the first day of the next month
  weekday: (s) => ((serial(2026, 1, 1) + s - 1 + 3) % 7) + 1,   // January 1, 2026 is a Thursday
  min: (a, b) => Math.min(a, b),
  workdays: (a, b) => { let n = 0;
    for (let s = a; s <= b; s++) if (FUNCS.weekday(s) <= 5) n++;
    return n; },
};
export function value(H, name) {
  const v = H[name];
  if (v === undefined) return 0;
  if (typeof v !== "string" || v[0] !== "=") return v;
  const expr = v.slice(1).replace(REF, (m) => JSON.stringify(value(H, m.replace(/\$/g, ""))));
  return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS));
}

export function sheet() {
  const H = {}, r = rng(SEED);
  H.A1 = "subscriber"; H.C1 = "consumption"; H.T1 = "opened"; H.U1 = "closed"; H.V1 = "reading day";
  for (let i = 0; i < ROWS; i++) {
    const s = i + 2;
    H["A" + s] = "AB-" + String(1041 + i * 7).padStart(4, "0");
    r();                                    // the region column from 01: rng runs in the same order
    H["C" + s] = Math.round((2 + r() * r() * 78) * 10) / 10;
    H["V" + s] = 1 + (i * 7) % 31;          // the reading day falls between the 1st and the 31st
  }
  // Support requests: opened in the first half of 2026, duration 0-20 days.
  const z = rng(SEED + 3);
  for (let i = 0; i < REQUESTS; i++) {
    const s = i + 2, b = serial(2026, 1, 1) + Math.floor(z() * 180);
    H["T" + s] = b;
    H["U" + s] = b + Math.floor(z() * z() * 21);
  }
  return H;
}

if (import.meta.url === `file://${process.argv[1]}`) {
  const H = sheet();
  const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
    : String(v).padEnd(B[0]))).join("");
  const y = g([8, 10, 16, 10]);
  const o = (f) => value({ ...H, X1: f }, "X1");
  console.log(y(["cell", "opened", "format(opened)", "closed"]));
  for (const s of [2, 4, 5, 55])
    console.log(y(["T" + s, H["T" + s], o(`=format(T${s})`), H["U" + s]]));
  const F = Array.from({ length: REQUESTS }, (_, i) => H["U" + (i + 2)] - H["T" + (i + 2)]);
  console.log(`\n${REQUESTS} support requests. Closed the same day (difference 0): ` +
    `${F.filter((v) => v === 0).length}. Raw difference total ${F.reduce((a, b) => a + b, 0)} days.`);
  console.log(`January 1, 2026 serial ${serial(2026, 1, 1)}, January 31, 2026 serial ${serial(2026, 1, 31)}, ` +
    `February 28, 2026 serial ${serial(2026, 2, 28)}; last day of February 2026 is ` +
    `${o("=format(monthEnd(date(2026,2,1)))")}.`);
}

cell        opened  format(opened)    closed
T2              51      20.02.2026        57
T4              72      13.03.2026        89
T5             148      28.05.2026       159
T55             69      10.03.2026        69

54 support requests. Closed the same day (difference 0): 6. Raw difference total 342 days.
January 1, 2026 serial 1, January 31, 2026 serial 31, February 28, 2026 serial 59; last day of February 2026 is 28.02.2026.

What sits in cell T2 is not 20.02.2026 but 51; the second and third columns are two readings of the same cell. The date a cell shows is an interpretation of the number inside it.

The last line sets up the month-end question: the last day of February 2026 is the 28th, and meters read on the 29th, 30th, or 31st of the month have no February counterpart.

Three Day-Counting Decisions

The same 54 requests are measured with three formulas: the first excludes the endpoints, the second counts both, the third counts only weekdays.

// duration.mjs — the same request set under three day-counting decisions: endpoints excluded, endpoints included, workdays.
import { sheet, value, shift, REQUESTS } from "./calendar.mjs";

const DECISIONS = [
  ["endpoints excluded", "=U2-T2"],
  ["endpoints included", "=U2-T2+1"],
  ["workdays (endpoints included)", "=workdays(T2,U2)"],
];
const H = sheet();
const column = (f) => { const K = { ...H };
  for (let i = 0; i < REQUESTS; i++) K["W" + (i + 2)] = shift(f, 0, i);
  return Array.from({ length: REQUESTS }, (_, i) => value(K, "W" + (i + 2))); };
const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
  : String(v).padEnd(B[0]))).join("");

const D = DECISIONS.map(([, f]) => column(f));
const y = g([30, 12, 12, 10]);
console.log(y(["W column formula", "total days", "mean", "zero"]));
DECISIONS.forEach(([name], i) => { const d = D[i], t = d.reduce((a, b) => a + b, 0);
  console.log(y([name, t, (t / REQUESTS).toFixed(2), d.filter((v) => v === 0).length])); });
console.log(`\n${REQUESTS} requests. Mean resolution time ` +
  DECISIONS.map(([name], i) => `${(D[i].reduce((a, b) => a + b, 0) / REQUESTS).toFixed(2)} (${name})`)
    .join(", ") + " days.");

// The requests that come out zero under any decision: zero is not a duration, it is a counting result.
const z = g([8, 14, 14, 12, 12, 10]);
console.log("\n" + z(["cell", "opened", "closed", "excluded", "included", "workdays"]));
const o = (f) => value({ ...H, X1: f }, "X1");
for (let i = 0; i < REQUESTS; i++) if (D[0][i] === 0 || D[2][i] === 0)
  console.log(z(["W" + (i + 2), o(`=format(T${i + 2})`), o(`=format(U${i + 2})`),
    D[0][i], D[1][i], D[2][i]]));

W column formula                total days        mean      zero
endpoints excluded                     342        6.33         6
endpoints included                     396        7.33         0
workdays (endpoints included)          281        5.20         2

54 requests. Mean resolution time 6.33 (endpoints excluded), 7.33 (endpoints included), 5.20 (workdays (endpoints included)) days.

cell            opened        closed    excluded    included  workdays
W14         10.06.2026    10.06.2026           0           1         1
W17         14.05.2026    14.05.2026           0           1         1
W26         19.01.2026    19.01.2026           0           1         1
W28         19.05.2026    19.05.2026           0           1         1
W49         06.03.2026    07.03.2026           1           2         0
W50         13.02.2026    14.02.2026           1           2         0
W51         06.05.2026    06.05.2026           0           1         1
W55         10.03.2026    10.03.2026           0           1         1

The number to report is the mean resolution time, and it has three values: 6.33, 7.33, and 5.20 days. The difference between the largest and the smallest is 41% of the smallest value. All three are defensible; the phrase “mean resolution time” does not say which one is meant.

The zero column shows where the decision breaks. In the endpoints-excluded count, 6 requests appear to have taken zero days; yet those requests were closed on the day they were opened, that is, they took one day. A report that reads the zeros as “resolved instantly” writes the result of the counting decision, not of the data.

The last two rows of the lower table give an extreme in the opposite direction: W49 and W50 took two days on the calendar, and because they fall entirely on a weekend, they show zero workdays. The same record carries three different durations under three counting rules.

When Is One Month Later

“One month” is not a fixed number of days. The block below computes the February reading under two rules, counts the ambiguity of dates converted to text, and scans the day-counting decision across four versions.

// month.mjs — "one month later" under two rules; and where the day-counting decision sits on the sheet.
import { sheet, value, shift, serial, REQUESTS, ROWS } from "./calendar.mjs";

const H = sheet();
const ev = (K, f) => value({ ...K, X9: f }, "X9");
const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
  : String(v).padEnd(B[0]))).join("");
// The January reading is day V of the month; the February reading is "one month later". Two rules give two dates.
const RULES = {
  clamp: "=date(2026,2,min(V2,day(monthEnd(date(2026,2,1)))))",
  overflow: "=date(2026,2,V2)",
};
const column = (K, c, n, f) => { const M = { ...K };
  for (let i = 0; i < n; i++) M[c + (i + 2)] = shift(f, 0, i);
  return Array.from({ length: n }, (_, i) => value(M, c + (i + 2))); };
const JAN = Array.from({ length: ROWS }, (_, i) => serial(2026, 1, H["V" + (i + 2)]));
const S = Object.fromEntries(Object.entries(RULES).map(([a, f]) =>
  [a, column(H, "Y", ROWS, f)]));

const y = g([10, 8, 14, 10, 14, 10]);
console.log(y(["cell", "reading", "clamp", "period", "overflow", "period"]));
for (const s of [2, 6, 15, 24]) { const i = s - 2;
  console.log(y(["V" + s, H["V" + s], ev(H, `=format(${S.clamp[i]})`), S.clamp[i] - JAN[i],
    ev(H, `=format(${S.overflow[i]})`), S.overflow[i] - JAN[i]])); }

const differ = S.clamp.filter((s, i) => s !== S.overflow[i]).length;
const escaped = S.overflow.filter((s) => ev(H, `=month(${s})`) !== 2).length;
console.log(`\nOf ${ROWS} subscribers, the two rules give different dates for ${differ} ` +
  `(reading day 29, 30, or 31). Under the overflow rule, the "February reading" of ${escaped} ` +
  `subscribers does not fall in February.`);

// A date entered as text: if day and month can swap places, the cell is read as two different days.
const swapped = Array.from({ length: REQUESTS }, (_, i) =>
  [H["T" + (i + 2)], ev(H, `=month(T${i + 2})`), ev(H, `=day(T${i + 2})`)])
  .filter(([, m, d]) => d <= 12 && d !== m)          // a day above 12 cannot swap places
  .map(([s, m, d]) => Math.abs(serial(2026, d, m) - s));
console.log(`Of ${REQUESTS} opening dates, ${swapped.length} can be read as two different days when ` +
  `entered as "DD.MM.YYYY" text; the largest deviation is ${Math.max(...swapped)} days.`);

// Read-back scan: where the day-counting decision sits in the formula text.
const P = { ...H, X1: "ends included (1/0)", X2: 1 };    // label on the left, value on the right
const VERSIONS = [["=U2-T2", "none"], ["=U2-T2+1", "embedded literal"],
  ["=U2-T2+$X$2", "parameter cell"], ["=workdays(T2,U2)", "function name"]];
const z = g([18, 24, 10, 10, 10]), sum = (d) => d.reduce((x, v) => x + v, 0);
console.log("\n" + z(["formula", "where the decision is", "total", "X2=0", "follow"]));
for (const [f, place] of VERSIONS) {
  const a = column(P, "W", REQUESTS, f), b = column({ ...P, X2: 0 }, "W", REQUESTS, f);
  console.log(z([f, place, sum(a), sum(b), a.filter((v, i) => v !== b[i]).length]));
}

cell       reading         clamp    period      overflow    period
V2               1    01.02.2026        31    01.02.2026        31
V6              29    28.02.2026        30    01.03.2026        31
V15             30    28.02.2026        29    02.03.2026        31
V24             31    28.02.2026        28    03.03.2026        31

Of 60 subscribers, the two rules give different dates for 6 (reading day 29, 30, or 31). Under the overflow rule, the "February reading" of 6 subscribers does not fall in February.
Of 54 opening dates, 19 can be read as two different days when entered as "DD.MM.YYYY" text; the largest deviation is 323 days.

formula              where the decision is     total      X2=0    follow
=U2-T2                                none       342       342         0
=U2-T2+1                  embedded literal       396       396         0
=U2-T2+$X$2                 parameter cell       396       342        54
=workdays(T2,U2)             function name       281       281         0

The first table puts the cost of the two rules side by side. Under the clamp rule, the three subscribers read on the 29th, 30th, and 31st of the month are gathered onto February 28 and their periods become 30, 29, and 28 days: the periods of three meters read on the same date have three different lengths, and no column on the sheet says so. Under the overflow rule the period is 31 days, but the February reading slides to March 1, 2, and 3: the February reading of 6 subscribers is not in February.

The text-date row measures a third ambiguity. In 19 of the 54 openings the day number is less than 12; when such a date is written as DD.MM.YYYY text, month and day can swap places and the cell is read as two different days — the largest deviation is 323 days. A date converted to text is a decision that cannot be read back.

The last table is the lesson’s second number. The first two versions do not hold the decision on the sheet: =U2-T2 does not say that the endpoints are excluded, and =U2-T2+1 shows a 1 but does not say what that 1 is. In the third, the decision is in cell X2, can be read in two steps by its label, and when it is changed 54 cells follow; in the fourth, the decision is in the name of the function. The results of the first two are 342 and 396; both are correct, both are without a source.

Summary

• A date in a cell is a number; the format is an interpretation of that number and does not change its content.
• The mean resolution time of the same 54 requests came out as 6.33, 7.33, and 5.20 days under three counting decisions; the difference is 41% of the smallest value.
• Zero is not a duration, it is a counting result: in the endpoints-excluded count 6 requests, and in the workday count 2 requests that fall on a weekend, showed zero.
• “One month later” gives two different dates at month end: the clamp rule produced three periods of 28, 29, and 30 days, and the overflow rule gave 31 days but shifted the February reading of 6 subscribers into March.
• The day-counting decision can be read back only when it is written into a parameter cell or a function name; an embedded +1 shows the number, not the decision. The 19 dates converted to text cannot be read back at all.

Next Step

Until now every formula looked at the same row. Yet the previous lesson assumed something: that the code in the visit record is found in the subscriber table and that the consumption against it is fetched. What does that job is a lookup function, and the lookup can be written in two different forms, by the column number or by the header name it is given. The two forms give the same result, until a column is inserted in the middle of the table. The next lesson counts, when a column is inserted, how many lookups break, how many return an error, and how many return a wrong value without an error.