Adding 30 days means a month
Month lengths and leap days differ. Use the declared DATE or EDATE target policy.
Rebuild dates and measure complete calendar intervals
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
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.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E6, E2, E3, E4, E5, E7, E8, E9, E10, E11, F8. Browser calculations may differ from your Excel version.
Rebuild calendar dates, then measure the interval
The explicit day belongs to DATE; EDATE retains the original start day and clamps it when the target month is shorter. Complete elapsed years/months follow the start day-of-month comparison. They are a separate calculation from a clamped renewal date.
| Result | Current value | Written formula |
|---|---|---|
| E2 · Start year | — | No formula |
| E3 · Start month | — | No formula |
| E4 · Start day | — | No formula |
| E5 · Rebuilt original date | — | No formula |
| E6 · DATE with explicit target day | — | No formula |
| E7 · EDATE preserving/clamping original day | — | No formula |
| E8 · Complete elapsed years | — | No formula |
| E9 · Total complete elapsed months | — | No formula |
| E10 · Remaining complete months | — | No formula |
| E11 · Elapsed calendar days | — | No formula |
| F8 · Joined complete interval | — | No formula |
Change an actual source value
Calendar date mode writes a whole numeric Excel date, without a clock or timezone. Use dates in 1901–2099, month shifts −24–24, explicit day 0–35 and year shifts −2–2. The complete shifted month and normalized date must stay in that century range. Reversed comparison dates are valid inputs and need Review order interval outputs; date reconstruction still works.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Extract and rebuild real date components, compare DATE overflow with month-end clamping, then report complete years/months and actual elapsed days.
THE QUICK ANSWER01
Extract numeric date parts, adjust them explicitly and let DATE normalize the requested day. Compare that result with EDATE before calculating a separate complete-year/month interval.
E2
=YEAR(B2)E3
=MONTH(B2)E4
=DAY(B2)E5
=DATE(E2,E3,E4)E6
=DATE(E2+B6,E3+B4,B5)E7
=EDATE(B2,12*B6+B4)E8
=IF(B3<B2,"Review order",DATEDIF(B2,B3,"Y"))E9
=IF(B3<B2,"Review order",DATEDIF(B2,B3,"M"))E10
=IF(B3<B2,"Review order",MOD(E9,12))E11
=IF(B3<B2,"Review order",B3-B2)F8
=IF(B3<B2,"Review order",E8&"y "&E10&"m")For the example data
E2 2023E3 1E4 31E5 2023-01-31 00:00E6 2023-03-03 00:00E7 2023-02-28 00:00E8 1E9 13E10 1E11 395F8 1y 1m
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
DATE normalization and EDATE clamping differ
Starting on 2023-01-31 with a one-month shift and explicit day 31, DATE reaches 2023-03-03. EDATE reaches 2023-02-28. A day-zero control instead chooses the preceding month end in the explicit construction. Neither expression silently defines the other policy.
Complete intervals retain the start day comparison
From 2024-02-29 through 2025-02-28, this task reports 0 complete years and 11 complete months. The clamped next-year date can already be February 28; that separate target calculation does not change the declared complete-interval convention.
Actual days are not fixed-length months or years
Subtract the numeric dates for actual elapsed calendar days. Do not divide by 30 or 365 to infer complete months or years. Equal endpoints produce 0y 0m and zero elapsed days; inclusive overlap counting has a different task.
Use actual numeric source dates
The calendar input mode accepts an actual date and writes its numeric serial. Invalid day combinations are refused; DATE normalization is applied only in the explicit output calculation. These exercises use whole dates without clock fractions, timezone or daylight-saving elapsed-time claims.
Read the full step-by-step walkthrough
- Extract the original components
Apply YEAR, MONTH and DAY separately in E2:E4. The stored B2 date remains a number; its regional display is not the input grammar.
- Rebuild before changing the policy
DATE(E2,E3,E4) in E5 must reproduce B2 exactly. This catches a wrong component before offsets make the result harder to inspect.
- Apply explicit normalization
E6 adds the signed year/month controls and uses the explicit target day. Day zero means the day before the target month starts; excess days continue into the following month.
- Compare the original-day clamp
E7 applies the combined month shift to B2. It retains the original day or the last available day in a short month. B5 belongs only to the explicit DATE construction.
- Guard reverse order and count complete years
For an earlier comparison date, return Review order. Otherwise DATEDIF Y counts complete years; subtracting the year numbers alone ignores whether the anniversary day has arrived.
- Keep full months and remainder distinct
DATEDIF M in E9 counts all complete months. E10 uses MOD(E9,12), so 13 total months becomes one remaining month after the full year. A fixed 30-day month is a different rule.
- Report elapsed days and the joined interval
E11 subtracts actual date serials; equal endpoints have zero days. F8 joins complete years and remaining months. No MD remaining-day calculation is implied.
Things to watch
The clamped target is used as the elapsed-year rule
Complete years/months follow the start day-of-month convention here. A clamped target date is a separate comparison.
Year subtraction overcounts the interval
The comparison month/day may precede the starting month/day. Check complete years rather than year labels alone.
Equal dates count as one day
This task measures elapsed days, not an inclusive covered-date count.
About this example
Original bounded Gregorian component/interval task; native date-system/region, recalculation and edit/save certification remain separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.