Best on desktop
The spreadsheet workspace needs more room than this screen offers. Switch to a laptop for the full experience.
A finance analyst's "calendar quarter-end date" formula is a 4-deep IF chain that hardcodes month numbers:
=DATE(YEAR(B2),
IF(MONTH(B2)<=3, 3,
IF(MONTH(B2)<=6, 6,
IF(MONTH(B2)<=9, 9, 12))),
IF(MONTH(B2)<=3, 31,
IF(MONTH(B2)<=6, 30,
IF(MONTH(B2)<=9, 30, 31))))
It works but encodes the calendar twice (once for the month, once for the day) and breaks if you ever want to change quarter boundaries.
The refactor pattern: EOMONTH walks N months forward and returns the last day of that month. The number of months to walk is 2 - MOD(MONTH(B2) - 1, 3) — that's how far each input month sits from the end of its quarter:
| MONTH(B2) | MOD(MONTH-1, 3) | Months to add | Quarter end |
|---|---|---|---|
| 1 (Jan) | 0 | 2 | Mar 31 |
| 2 (Feb) | 1 | 1 | Mar 31 |
| 3 (Mar) | 2 | 0 | Mar 31 |
| 4 (Apr) | 0 | 2 | Jun 30 |
| 7 (Jul) | 0 | 2 | Sep 30 |
| 12 (Dec) | 2 | 0 | Dec 31 |
Wrap with LET to bind the date once:
=LET(d, B2, EOMONTH(d, 2 - MOD(MONTH(d) - 1, 3)))
EOMONTH handles the month-end day automatically (Mar 31, Jun 30, Sep 30, Dec 31 are all just "end of that month") so we don't have to encode the day-count rule.
Your task:
yyyy-mm-dd string.Wrap the EOMONTH call with TEXT for deterministic string output: =TEXT(LET(d, B2, EOMONTH(d, 2 - MOD(MONTH(d) - 1, 3))), "yyyy-mm-dd").
Graded cells: C2 (2026-01-15 → 2026-03-31), C8 (2026-08-30 → 2026-09-30), C13 (2026-12-25 → 2026-12-31).