Skip to main content

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.

January overflow versus a shorter target monthExtract the original calendar components0 cells selected
Dates are displayed as calendar dates but remain numbers. Choose source cells, then reason about the stated calendar policy.
Click or drag source cells; selected cells change color Drag from selected cells to deselect.
#
1
2
3
4
5
Step 1 of 6

Extract the original calendar components

Select the source cells in the worksheet, then check your selection.

Rules for this step

YEAR, MONTH and DAY read the stored start date. Regional formatting does not change these numeric components.

Start year · E2
Start month · E3
Start day · E4
0 cells selected