How to get the quarter-end date for any date
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Snap a date to its quarter's last day — reporting cutoffs, fiscal deadlines.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =EOMONTH(DATE(YEAR(A2),CEILING(MONTH(A2)/3,1)*3,1),0) | CEILING(month/3)*3 finds the quarter's last month; EOMONTH finds its last day. |
| Google Sheets | =EOMONTH(DATE(YEAR(A2),CEILING(MONTH(A2)/3,1)*3,1),0) | Identical. |
| LibreOffice Calc | =EOMONTH(DATE(YEAR(A2),CEILING(MONTH(A2)/3,1)*3,1),0) | Identical. |
How it works
Month 7 divided by 3 and ceiled gives quarter 3; times 3 gives its closing month (September); EOMONTH lands on the 30th. The quarter-START date is the same construction minus two months: DATE(YEAR(A2),CEILING(MONTH(A2)/3,1)*3-2,1). For fiscal years that don't start in January, shift the month first with MOD before the arithmetic — or add a helper column, which everyone can read.
Verified, not just documented
We ran =TEXT(EOMONTH(DATE(2026,CEILING(MONTH(DATE(2026,7,26))/3,1)*3,1),0),"YYYY-MM-DD") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2026-09-30 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2026-09-30 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
EOMONTH · DATE · YEAR · CEILING · MONTH — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to check if a year is a leap year
- How to get the day number of the year (1-365)
- How to create a quarter-and-year label like "Q3 2026"
- How to sum values by month
- How to calculate a year-to-date (YTD) total