Excel Quarter Start & End Dates: Formula Guide
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to calculate the start date and end date of a fiscal or calendar quarter for any given date in Microsoft Excel. You will learn the core formulas using DATE, YEAR, and CHOOSE, how to adapt them for fiscal years that don’t align with January, and how to handle common edge cases like year-end boundaries.
Formula
For a calendar quarter (Jan–Mar, Apr–Jun, Jul–Sep, Oct–Dec), the formulas are:
- Quarter start date:
=DATE(YEAR(A1), CHOOSE(CEILING(MONTH(A1)/3,1), 1, 4, 7, 10), 1) - Quarter end date:
=DATE(YEAR(A1), CHOOSE(CEILING(MONTH(A1)/3,1), 3, 6, 9, 12) + 1, 0) - Quarter number:
=CEILING(MONTH(A1)/3, 1)
Where A1 contains a date. MONTH(A1) returns 1–12. Dividing by 3 and rounding up (CEILING) gives 1, 2, 3, or 4. CHOOSE maps that number to the first month of the quarter. The end date uses month + 1 and day 0, which Excel interprets as the last day of the previous month.
Worked Example
Using the default date 2024-05-14 (May 14, 2024):
MONTH(A1)= 5- 5 / 3 = 1.6667,
CEILING(1.6667, 1)= 2 (Q2) CHOOSE(2, 1, 4, 7, 10)= 4 (April)- Start date:
DATE(2024, 4, 1)= April 1, 2024 - End date:
DATE(2024, 4+1, 0)=DATE(2024, 5, 0)= April 30, 2024
The calculator above gives the same result: Q2 start = 2024-04-01, end = 2024-04-30.
How the Formulas Work
Excel stores dates as serial numbers, so adding or subtracting days is straightforward. The key trick is using CHOOSE with the quarter number to pick the first month. For the end date, instead of calculating the last day directly, you set the day to 0 of the following month—Excel automatically rolls back to the last day of the prior month. This works for every quarter, including Q4, because DATE(2024, 13, 0) returns December 31, 2024.
Fiscal Quarters (Non-Calendar Years)
Many companies use fiscal quarters that start in February, July, or October. To adapt, replace the CHOOSE list with your fiscal start months. For example, if your fiscal year starts in July (Q1 = Jul–Sep):
- Start:
=DATE(YEAR(A1), CHOOSE(CEILING(MONTH(A1)/3,1), 7, 10, 1, 4), 1) - End:
=DATE(YEAR(A1), CHOOSE(CEILING(MONTH(A1)/3,1), 9, 12, 3, 6) + 1, 0)
Note that for months in Jan–Mar, the fiscal year may be different from the calendar year. You may need to add a year adjustment if your fiscal year starts in a later month.
Common Mistakes and Edge Cases
- Using
ROUNDinstead ofCEILING:ROUND(MONTH(A1)/3,0)gives 1 for April (4/3=1.33) but 2 for May (5/3=1.67), which is wrong. Always useCEILING. - Forgetting the day 0 trick: Some users try
DATE(YEAR, month, 31)but that fails for months with 30 days or February. - Year-end for fiscal quarters: If your fiscal year starts in October, Q1 ends in December, but Q2 ends in March of the next calendar year. Your formula must account for that.
Alternatives for Older Excel Versions
If you don’t have CEILING (unlikely, it’s been in Excel since 2007), you can use =INT((MONTH(A1)+2)/3) to get the quarter number. For the start month, you can also use =CHOOSE(INT((MONTH(A1)+2)/3), 1, 4, 7, 10). This works because adding 2 before integer division always rounds up correctly.
Frequently Asked Questions
How do I calculate the quarter end date if the date is in a different year?
The formula handles year boundaries automatically. For December 2024, CEILING(12/3,1) = 4, so the end date is DATE(2024, 12+1, 0) = December 31, 2024. For January 2025, it correctly gives March 31, 2025.
Can I use these formulas with a date stored as text?
No—Excel needs a real date serial number. If your text looks like a date, use DATEVALUE first, or convert the cell to a date format.
What if my fiscal year starts in April?
Replace the CHOOSE lists with 4, 7, 10, 1 for starts and 6, 9, 12, 3 for ends. For months Jan–Mar, you’ll need to subtract 1 from the year because those months belong to the previous fiscal year.
How do I display the quarter as “Q1 2024”?
Use ="Q"&CEILING(MONTH(A1)/3,1)&" "&YEAR(A1). For fiscal years, replace YEAR(A1) with your adjusted year formula.
Is there a simpler way using EOMONTH?
Yes, for the end date you can use =EOMONTH(DATE(YEAR(A1), CHOOSE(CEILING(MONTH(A1)/3,1), 3, 6, 9, 12), 1), 0). It returns the last day of that month directly.