Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the last day of a calendar quarter
Excel & Google Sheets
=TEXT(DATE(A2,3*ROUNDUP(B2/3,0)+1,0),"yyyy-mm-dd")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Year | Month |
| 2026 | 12 |
=TEXT(DATE(A2,3*ROUNDUP(B2/3,0)+1,0),"yyyy-mm-dd")→2026-12-31
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter the year in A2 and a month from 1 to 12 in B2.
- 2ROUNDUP(B2/3,0) returns a quarter number from 1 to 4.
- 3DATE(year,3*quarter+1,0) takes the day before the following quarter starts. Month 13 correctly rolls into January of the next year.
- 4Remove TEXT for a numeric result and apply Date formatting when using the date in other calculations.
Tips
- This is the calendar quarter convention ending in March, June, September or December; fiscal calendars need an explicit offset or calendar table.
- Validate the month before calculation so an invalid number is not silently normalized into a different quarter.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does December return a date in the wrong year?
No. Day zero of January 2027 is 31 December 2026.
Is this the last working day of the quarter?
No, it is the calendar day. Weekend and holiday adjustment is a separate step.
Sources and compatibility
Reviewed 2026-09-16