Excel Calendar Quarter Formula: How to Extract Q1-Q4

Formulas & functions · Updated November 22, 2026

The simplest Excel formula to get the calendar quarter from a date in cell A1 is =ROUNDUP(MONTH(A1)/3,0). This returns 1 for Jan–Mar, 2 for Apr–Jun, 3 for Jul–Sep, and 4 for Oct–Dec. For a text label like “Q1”, use ="Q"&ROUNDUP(MONTH(A1)/3,0).

Why the Quarter Matters in Excel

Calendar quarters are a standard way to group financial, sales, and operational data. Excel has no built-in QUARTER function, so you need to build the logic yourself. The formula above is the most direct method, but there are several variations depending on whether your fiscal year starts in April, whether you want a text label, or whether you need to handle blank cells gracefully.

How the ROUNDUP Formula Works

Excel stores dates as serial numbers, but the MONTH function extracts the month number (1–12) from any valid date. Dividing that month by 3 gives a decimal: January (1/3 = 0.33), February (2/3 = 0.67), March (3/3 = 1), April (4/3 = 1.33), and so on. ROUNDUP with zero decimal places rounds any decimal up to the next whole number, so 0.33 becomes 1, 1.33 becomes 2, and 2.67 becomes 3. This maps each three-month block to a single quarter.

For example, with the date 15-May-2025 in A1, MONTH(A1) returns 5, 5/3 is 1.6667, and ROUNDUP(1.6667,0) gives 2. The formula returns 2, which is the correct calendar quarter for May.

How to Display “Q1”, “Q2”, etc.

If you want a text label like “Q1” instead of a number, concatenate the letter Q with the result. In cell B1, with the date in A1, use:

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

This returns the text string “Q2” for a May date. If you prefer “Quarter 1” or “1st Quarter”, adjust the text accordingly: ="Quarter "&ROUNDUP(MONTH(A1)/3,0).

Using a CHOOSE Formula as an Alternative

Another common approach uses CHOOSE to map each month directly to a quarter number. In cell B1:

=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)

This works by listing the quarter for each month in order: January=1, February=1, March=1, April=2, and so on. It is slightly longer but easier to read for some users. The result is identical to the ROUNDUP method. For a fiscal year that starts in April, you can adjust the CHOOSE list to =CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3), which maps Jan–Mar to Q4, Apr–Jun to Q1, etc.

Handling Blank Cells and Errors

If the date cell is empty, MONTH(A1) returns 1 because Excel treats a blank as 0, and January is month 1. That gives a false Q1. To avoid this, wrap the formula in an IF check:

=IF(A1="","",ROUNDUP(MONTH(A1)/3,0))

This returns an empty string if A1 is blank, otherwise the quarter number. If A1 contains text instead of a date, MONTH will return a #VALUE! error. You can suppress that with =IFERROR(ROUNDUP(MONTH(A1)/3,0),""), but be aware that IFERROR will also hide real errors, so use it only when you expect text inputs.

Fiscal Year Quarters (Non-Calendar)

Many companies use a fiscal year that does not start in January. To calculate a fiscal quarter, you need to shift the months. A general formula for a fiscal year starting in month s (where January=1, February=2, etc.) is:

=MOD(ROUNDUP((MONTH(A1)-s+12)/3,0)-1,4)+1

For example, if your fiscal year starts in April (s=4), the formula becomes:

=MOD(ROUNDUP((MONTH(A1)-4+12)/3,0)-1,4)+1

Test it with a May date: MONTH=5, (5-4+12)=13, 13/3=4.33, ROUNDUP=5, MOD(5-1,4)+1 = MOD(4,4)+1 = 0+1 = 1. So May is fiscal Q1, which is correct for an April start. For a January date: MONTH=1, (1-4+12)=9, 9/3=3, ROUNDUP=3, MOD(3-1,4)+1 = MOD(2,4)+1 = 2+1 = 3. January is fiscal Q3, which is correct because the fiscal year runs Apr–Mar.

Quarter Start or End Dates

Sometimes you need the first or last date of the quarter, not just the number. To get the start date of the quarter for a date in A1, use:

=DATE(YEAR(A1),CHOOSE(MONTH(A1),1,1,1,4,4,4,7,7,7,10,10,10),1)

This returns January 1, April 1, July 1, or October 1 of the same year. To get the end date, use:

=DATE(YEAR(A1),CHOOSE(MONTH(A1),3,3,3,6,6,6,9,9,9,12,12,12)+1,0)

The trick with +1,0 in the DATE function returns the last day of the month before the next month. For a May date, the CHOOSE returns 6, then DATE(YEAR,6+1,0) = DATE(YEAR,7,0) = June 30, which is the quarter end.

Grouping by Quarter in PivotTables

If you have a list of dates, you can group them by quarter directly in a PivotTable without any formula. Right-click a date field in the PivotTable, choose Group, then select Quarters (and optionally Years). This creates a quarter grouping automatically. However, the grouping is based on the calendar year, so if you need fiscal quarters, you must create a helper column with the formulas above and use that in the PivotTable.

Common Problems and Misconceptions

A frequent error is using =INT(MONTH(A1)/3) or =ROUND(MONTH(A1)/3,0). INT truncates decimals, so for April (4/3=1.33) it returns 1 instead of 2, giving the wrong quarter. ROUND rounds to the nearest integer, so for May (1.67) it returns 2, but for February (0.67) it also returns 1, which is correct, but for March (1.00) it returns 1, which is correct, but the rounding behavior is inconsistent for months that are exactly at the boundary—actually all months work with ROUND, but INT fails for months 4,5,7,8,10,11. Always use ROUNDUP to ensure the quarter increments exactly at months 1,4,7,10.

Another misconception is that Excel has a built-in QUARTER function. It does not, but some users confuse it with the QTR function in other spreadsheet software. Stick with the formulas above.

Frequently Asked Questions

Can I use the quarter formula in Google Sheets?

Yes, all the formulas in this article work identically in Google Sheets because they use standard functions like MONTH, ROUNDUP, CHOOSE, and DATE. The only difference is that Google Sheets uses the same syntax, so you can copy the formulas directly.

How do I get the quarter as “Q1-2025”?

Combine the quarter and year with a hyphen: ="Q"&ROUNDUP(MONTH(A1)/3,0)&"-"&YEAR(A1). This returns “Q2-2025” for a May 2025 date. For a fiscal year that differs from the calendar year, adjust the year calculation accordingly.

What if my dates are in a different format, like “2025-05-15”?

Excel stores dates as serial numbers regardless of how they are displayed. As long as the cell contains a real date (not text), the MONTH function will work. If the cell contains text, you may need to convert it with DATEVALUE first, but that can be unreliable. It is best to ensure your dates are entered as actual date values.

Does the formula work with time values?

Yes, because a time value is a fraction of a day, but MONTH still extracts the month from the date portion. For example, 15-May-2025 14:30 is still month 5. The time component is ignored by MONTH.

How do I count sales per quarter using a formula?

Use SUMPRODUCT to sum values where the quarter matches. For example, with dates in A2:A100 and amounts in B2:B100, to sum Q1 amounts: =SUMPRODUCT((ROUNDUP(MONTH(A2:A100)/3,0)=1)*B2:B100). This works in Excel 365 and older versions. Alternatively, use a helper column with the quarter formula and then SUMIF.

Why does my formula return #VALUE!?

This usually means the cell contains text that Excel cannot interpret as a date. Check that the cell is formatted as a date and that the value is not a string like “May 15”. Use the ISDATE function (in Excel 365) or the ISNUMBER function to test: =ISNUMBER(A1) returns TRUE if A1 is a date serial number.

Final Tips for Using Quarter Formulas

  • Always test your formula with a known date, such as 15-Jan-2025, 15-Apr-2025, 15-Jul-2025, and 15-Oct-2025, to verify the output is 1, 2, 3, and 4.
  • If you need the quarter for many rows, consider adding a helper column with the formula and then using PivotTables or SUMIFS for aggregation.
  • For dynamic arrays in Excel 365, you can use =ROUNDUP(MONTH(A2:A100)/3,0) in a single cell and it will spill down automatically.
  • Remember that the ROUNDUP formula is the most reliable because it consistently rounds up at the quarter boundaries.

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.