Rebuild dates and measure complete calendar intervals
Your goal: Extract and rebuild real date components, compare DATE overflow with month-end clamping, then report complete years/months and actual elapsed days.
Full task requirements
Build E2:E11 and F8. Extract the start year/month/day and rebuild its numeric date. E6 uses DATE(start year + year shift, start month + month shift, explicit target day), allowing the declared day-zero/overflow normalization. E7 shifts the original date by 12 × year shift + month shift, preserving its day when available and otherwise clamping to the last target-month day. These two target policies are separate. For comparison >= start, E8 counts full years, E9 total complete months, E10 complete months remaining after full years, E11 actual elapsed days without inclusive +1, and F8 joins E8 and E10 as a compact label, for example 1y 1m. Complete-year/month intervals follow the original day-of-month comparison, not an anniversary-clamping rule. A reversed comparison returns Review order in E8:E11/F8 while date reconstruction remains numeric. Source dates and both the shifted target month and normalized result stay in 1901–2099; month shifts −24–24, explicit day 0–35 and year shifts −2–2 are whole numbers. Text/Boolean/blank/fractional dates and unsupported derived ranges remain ungraded.
| # | ||
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 |
Extract the original calendar components
Rules for this step
YEAR, MONTH and DAY read the stored start date. Regional formatting does not change these numeric components.