Date & Time
Classify elapsed calendar days into 0–30, 31–60, 61–90 and 91+ buckets
Calculate elapsed days between an onboarding, ticket, or trial start date and a report cutoff date, then map into clean tenure bands via table-driven XLOOKUP.
Replace brittle 5-level nested IFs with a maintainable day-bucket lookup table!
This example uses four nonoverlapping calendar-day bands with lower bounds 0, 31, 61 and 91. The final label is 91+; 90 belongs in the preceding band. A fixed cutoff makes the report reproducible.
Inputs must be whole nonnegative spreadsheet date serials. Time-of-day fractions are rejected instead of being silently described as whole days. This is elapsed calendar time, not business days, inclusive duration or days overdue.
| Start Date (A) | Cutoff (2026-06-01) | Elapsed Days | Assigned Cohort Band |
|---|---|---|---|
| 2026-05-20 | 2026-06-01 | 12 days | "0-30 Days" (Cutoff >= 0) |
| 2026-04-15 | 2026-06-01 | 47 days | "31-60 Days" (Cutoff >= 31) |
| 2026-03-10 | 2026-06-01 | 83 days | "61-90 Days" (Cutoff >= 61) |
| 2026-01-15 | 2026-06-01 | 137 days | "90+ Days" (Cutoff >= 91) |
Assign elapsed day-range bucket from Start Date A2 and Cutoff E2
Guards future start dates (A2 > $E$2) first, then uses XLOOKUP match_mode = -1 against sorted lower-bound thresholds.
=IF(OR(COUNT(A2,$E$2)<>2,COUNT(G2:G5)<>4),"Invalid inputs",IF(OR(A2<0,$E$2<0,A2<>INT(A2),$E$2<>INT($E$2),G2<>0,G3<>31,G4<>61,G5<>91,COUNTBLANK(H2:H5)>0),"Invalid inputs",IF(A2 > $E$2, "Future Date", XLOOKUP($E$2 - A2, $G$2:$G$5, $H$2:$H$5, "Invalid", -1))))Exact elapsed calendar days since start
Subtract whole date serials. This signed difference is negative for a future start; the classification formula instead returns Future Date. The lookup example locks the documented lower bounds, while nonempty display labels may be translated.
=IF(COUNT(A3,$E$2)<>2,"Invalid inputs",IF(OR(A3<0,$E$2<0,A3<>INT(A3),$E$2<>INT($E$2)),"Invalid inputs",$E$2 - A3))Try the example
Use the original values or change them to test your understanding.