Excel Formula for Calculating Quarter

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for calculating the quarter from a date in cell A1 is =ROUNDUP(MONTH(A1)/3,0), which returns 1, 2, 3, or 4. For a text result like “Q1”, use ="Q"&ROUNDUP(MONTH(A1)/3,0).

Why You Might Need a Quarter Formula

Financial and operational reporting often groups data by fiscal periods. While Excel has built-in functions for year, month, and day, there is no dedicated QUARTER function. Analysts, accountants, and project managers frequently need to summarize sales, expenses, or KPIs by quarter. A reliable formula turns any date column into a quarter label that can be used in pivot tables, charts, or SUMIFS criteria.

How the Formula Works

The core formula uses two functions: MONTH() and ROUNDUP(). MONTH(A1) extracts the month number from a date (1 for January, 2 for February, etc.). Dividing that number by 3 gives values from 0.333 (January) to 4.0 (December). ROUNDUP(...,0) rounds any fractional value up to the nearest integer, so months 1–3 become 1, months 4–6 become 2, months 7–9 become 3, and months 10–12 become 4.

For example, if A1 contains 2025-05-14 (May 14, 2025), MONTH(A1) returns 5, 5/3 = 1.6667, and ROUNDUP(1.6667,0) returns 2. The formula outputs 2, meaning the second quarter.

Adding a “Q” Prefix and Year

Most reports prefer labels like “Q1-2025” over a bare number. To prepend the letter Q, concatenate it with the result: ="Q"&ROUNDUP(MONTH(A1)/3,0). To include the year, use ="Q"&ROUNDUP(MONTH(A1)/3,0)&"-"&YEAR(A1). This produces a text string like “Q2-2025”.

If you need a numeric year-quarter value (e.g., 20251 for Q1 of 2025), use =YEAR(A1)*10+ROUNDUP(MONTH(A1)/3,0). This returns 20251, 20252, etc., which sorts chronologically in Excel.

Fiscal Year Quarters (Non-Calendar)

Many companies use a fiscal year that starts in a month other than January. For a fiscal year starting in April, the first quarter runs April–June. The formula adjusts by shifting the month number: =ROUNDUP(MOD(MONTH(A1)-3,12)/3,0). Here, subtracting 3 makes April (month 4) become 1, May becomes 2, June becomes 3, and so on. The MOD function handles the wrap-around for January (month 1 becomes -2, which MOD turns into 10, giving quarter 4).

To make the fiscal year label, combine with the fiscal year number: ="FY"&YEAR(A1)+(MONTH(A1)>=4)&"-Q"&ROUNDUP(MOD(MONTH(A1)-3,12)/3,0). This adds 1 to the calendar year if the month is April or later.

Using the CHOOSE Function for Custom Labels

If you need custom quarter names (e.g., “Spring”, “Summer”, or “Qtr1”), use CHOOSE: =CHOOSE(ROUNDUP(MONTH(A1)/3,0),"Q1","Q2","Q3","Q4"). The first argument is the quarter number, and the following four arguments are the labels for quarters 1 through 4. This is more flexible than the concatenation approach because you can use any text, including spaces or symbols.

Common Problems and Solutions

  • Blank cells: If A1 is empty, MONTH(A1) returns 1 (because Excel treats blank as 0, and month 0 becomes January). The formula returns 1. To avoid this, wrap with IF(A1="","",...).
  • Text dates: If A1 contains a text string like “05/14/2025”, MONTH may still work if Excel recognizes it as a date, but it is safer to use DATEVALUE first. The formula becomes =ROUNDUP(MONTH(DATEVALUE(A1))/3,0).
  • Dates stored as serial numbers: Excel stores dates as numbers. If your cell shows a number like 45000, it is a valid date; the formula will work correctly.
  • Whole column references: If you use =ROUNDUP(MONTH(A:A)/3,0) in a single cell, it will return a #VALUE! error because MONTH cannot handle a range. Use it in a helper column instead.

Comparing with Pivot Table Grouping

You might not need a formula at all. Excel pivot tables can group dates by quarter automatically. Right-click a date field in a pivot table, choose Group, then select Quarters and optionally Years. This is faster for ad-hoc analysis but does not create a reusable column in your data. The formula approach is better when you need a permanent field for VLOOKUP, SUMIFS, or charting.

Frequently Asked Questions

How do I calculate the quarter for a date in Google Sheets?

Google Sheets uses the same functions: =ROUNDUP(MONTH(A1)/3,0) works identically. The only difference is that in Google Sheets, you might use ARRAYFORMULA to apply it to a range: =ARRAYFORMULA(ROUNDUP(MONTH(A1:A10)/3,0)).

Can I calculate the quarter from a month number instead of a date?

Yes. If you have a month number in B1 (1–12), use =ROUNDUP(B1/3,0). This is simpler because you skip the MONTH function.

What if my fiscal year starts in July?

Replace the shift value in the fiscal formula. For a July start, subtract 6: =ROUNDUP(MOD(MONTH(A1)-6,12)/3,0). July becomes month 1, August month 2, etc. The fiscal year label would be ="FY"&YEAR(A1)+(MONTH(A1)>=7)&"-Q"&ROUNDUP(MOD(MONTH(A1)-6,12)/3,0).

Why does my formula return 1 for a blank cell?

Because Excel treats a blank cell as 0, and month 0 is interpreted as January. To return an empty string, use =IF(A1="","",ROUNDUP(MONTH(A1)/3,0)).

Is there a QUARTER function in Excel?

No. Excel does not have a built-in QUARTER function as of Microsoft 365. The workaround is the ROUNDUP/MONTH combination shown above. Some add-ins or VBA custom functions exist, but the native formula is sufficient for most needs.

How do I get the quarter as a date (e.g., start of quarter)?

To get the first date of the quarter for a date in A1, use =DATE(YEAR(A1),CHOOSE(ROUNDUP(MONTH(A1)/3,0),1,4,7,10),1). This returns January 1, April 1, July 1, or October 1 of the same year.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.