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 withIF(A1="","",...). - Text dates: If A1 contains a text string like “05/14/2025”,
MONTHmay still work if Excel recognizes it as a date, but it is safer to useDATEVALUEfirst. 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
- SUBSTRING in Snowflake: Syntax, Examples, and Tips
- Excel Auto Loan Amortization Template: How to Build One
- Excel Audit Process: Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Copy Formulas in Excel Without Creating External LinksTo copy a formula without linking it to another workbook, copy the formula text from the formula bar…
- How to Show Formulas in Excel for MacTo show formulas in Excel for Mac, open the worksheet and choose Formulas > Show Formulas. You can…
- Find the Highest Value in Excel with the MAX FunctionTo find the highest number in an Excel range, enter =MAX(B2:B20) in an empty cell, replacing B2:B20 with…
- Power Query Nested If: Syntax and ExamplesIn Power Query, write a nested conditional as if condition1 then result1 else if condition2 then result2 else…
- ZN Function in Tableau: What It Does and When to Use ItIn Tableau, ZN(expression) returns the expression’s value when it is not null, and returns 0 when it is…
- Excel COUNTIF Then Formula: How to Use ItThe direct answer: there is no single “COUNTIF then” function, but you combine COUNTIF with IF to perform…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.