Get the Quarter From a Date in Excel

To get the quarter from a date in Excel, divide the month by 3 and round up: =ROUNDUP(MONTH(A2)/3,0) returns 1, 2, 3 or 4. Excel has no QUARTER function, so this short formula is the standard approach. This guide is for anyone building monthly or quarterly reports, and it also covers Q1–Q4 labels, fiscal years, quarter start and end dates, and totals by quarter.

How to get the quarter from a date#

Combine MONTH and ROUNDUP. MONTH gives the month number from 1 to 12, and dividing by 3 then rounding up groups those months into fours.

=ROUNDUP(MONTH(A2)/3,0)

The 0 in ROUNDUP means "round up to a whole number". Any month in the same quarter gives the same result, so March 31 is still Q1.

You'll also see =INT((MONTH(A2)+2)/3). It returns the same 1 to 4 and works in every version of Excel. Use whichever reads more clearly to you.

If MONTH returns odd results, the "dates" may be text. Our guide to why Excel stores dates as numbers explains how to tell, and DATE, YEAR, MONTH and DAY covers MONTH itself.

How to show the quarter as Q1, Q2, Q3, Q4#

Join the letter Q to the number with &:

="Q"&ROUNDUP(MONTH(A2)/3,0)

Here are some orders labelled this way:

When your data covers more than one year, add the year so Q1 2024 and Q1 2025 stay separate:

="Q"&ROUNDUP(MONTH(A2)/3,0)&" "&YEAR(A2)

This returns labels like Q3 2025. For labels that sort correctly as text, put the year first: =YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0) gives 2025-Q3, and 2024 labels sort before 2025 ones.

Label a whole column in one formula#

In Excel 2021, Excel 2024 and Microsoft 365, you can give the formula a whole range and let the results spill:

="Q"&ROUNDUP(MONTH(B2:B6)/3,0)

Typed once in C2, it fills C2:C6 with a label for every order. There's nothing to fill down, and if you lengthen the range, the labels extend with it. In older versions, write the single-cell formula in C2 and fill it down with Ctrl+D (Windows) or ⌘D (Mac).

How to get the fiscal quarter#

Shift the date back by the number of months between January and your fiscal year's first month, then use the same formula. If your fiscal year starts in July, move each date back 6 months:

=ROUNDUP(MONTH(EDATE(A2,-6))/3,0)

With a July start, August 14, 2025 moves back to February, so it is fiscal Q1. EDATE moves a date by whole months; our guide to adding days, months or years to a date explains how.

The fiscal year label depends on your company's rule. If the fiscal year is named after the year it ends in, a July start gives =YEAR(EDATE(A2,6)): August 2025 is in fiscal year 2026.

How to get the first and last day of the quarter#

Build the quarter's first day with DATE. The quarter's first month is (quarter − 1) × 3 + 1:

=DATE(YEAR(A2),(ROUNDUP(MONTH(A2)/3,0)-1)*3+1,1)

For August 14, 2025 (Q3), this returns July 1, 2025.

For the last day, find the quarter's last month (quarter × 3) and jump to its month-end with EOMONTH:

=EOMONTH(DATE(YEAR(A2),ROUNDUP(MONTH(A2)/3,0)*3,1),0)

This returns September 30, 2025. Pair it with a countdown to see how many days are left in the quarter; our guide to counting the days until a deadline shows how.

How to total sales by quarter#

Add a quarter column with the formula above, then use SUMIFS or COUNTIFS on it. With amounts in D2:D100 and labels like "Q3 2025" in E2:E100:

=SUMIFS($D$2:$D$100,$E$2:$E$100,"Q3 2025")

Without a helper column, SUMPRODUCT can test the quarter directly:

=SUMPRODUCT((ROUNDUP(MONTH(B2:B100)/3,0)=3)*(YEAR(B2:B100)=2025)*D2:D100)

Each test returns 1 or 0 per row, and multiplying keeps only rows where both are true. Make sure B2:B100 has no blank cells, because MONTH of a blank is 1 and would count as January.

Group by quarter in a PivotTable#

You don't need any formula in a PivotTable. Put the date field in Rows, right-click any date, choose Group, and select Quarters and Years. Selecting Years as well keeps Q1 2024 apart from Q1 2025. In recent versions of Excel, dates may be grouped automatically when you add them to the PivotTable.

How to get the current quarter#

Use TODAY() as the date to label the quarter you're in now:

="Q"&ROUNDUP(MONTH(TODAY())/3,0)

TODAY() updates each time the workbook recalculates, so a dashboard header always shows the current quarter. If you need the time as well, see TODAY vs NOW. And for part-of-year calculations, such as how much of the year has passed, YEARFRAC returns the fraction directly.

Common mistakes with quarter formulas#

Watch for these:

• Rounding the wrong way. ROUND(MONTH(A2)/3,0) puts January in quarter 0 and April in Q1. Use ROUNDUP.
• Forgetting the year. Quarters from different years merge in totals unless you include YEAR.
• Text dates. MONTH returns #VALUE! on text that Excel can't read as a date.
• Blank cells in array formulas. MONTH of an empty cell is 1, so blanks look like January.

Practise it#

Try the exercise to label orders Q1 to Q4. The quarter formula is built on MONTH and YEAR, which you can practise in the DATE, YEAR, MONTH and DAY lesson, where you build dates from parts and pull them apart in a live spreadsheet.