With a valid Excel date in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from 1 to 4. To return a combined label such as 2026 Q2, use =YEAR(A2)&" Q"&ROUNDUP(MONTH(A2)/3,0).
How calendar quarters work
A calendar year is divided into four three-month quarters: Q1 is January through March, Q2 is April through June, Q3 is July through September, and Q4 is October through December. Excel stores recognized dates as serial numbers, so functions such as MONTH and YEAR can extract the relevant parts of a date and use them in a quarter calculation.
The quarter formula works by dividing the date’s month number by three and rounding up to the next whole number. January, February, and March therefore return 1; April through June return 2; July through September return 3; and October through December return 4. For example, a date in May has month number 5, and rounding 5 divided by 3 up gives quarter 2.
These formulas use calendar quarters. They do not automatically follow an organization’s financial year, which may begin in a month other than January. Use the fiscal-quarter method below if your reporting calendar starts in a different month.
Return the quarter number or a year-quarter label
With a valid date in A2, the formula =ROUNDUP(MONTH(A2)/3,0) returns only the quarter number. For example, if A2 contains 15-May-2026, the result is 2. Format or name the result column as needed, such as Quarter, and keep the value numeric if you will use it for calculations.
With the date in A2, use =YEAR(A2)&" Q"&ROUNDUP(MONTH(A2)/3,0) to combine the year and quarter into a readable text label. For 15-May-2026, the result is 2026 Q2. Fill the formula down to classify other rows; Excel adjusts the cell reference for each row.
The combined label is convenient for chart labels, reports, or a pivot-table source field. It is text, however, not a date or a numeric period value. Text labels can sort alphabetically rather than chronologically in some contexts, especially when labels use inconsistent formats. If you need to calculate, filter, or sort periods reliably, keep the year and quarter in separate numeric columns, or use an actual quarter-start date as a sortable date field.
Build a formula with INT instead
With a valid date in A2, =INT((MONTH(A2)-1)/3)+1 also returns the calendar quarter number. It subtracts one from the month, divides by three, discards the decimal portion, and adds one. This is a common alternative to ROUNDUP and gives the same answers for all twelve months.
For instance, for a date in September, the calculation uses month 9: INT((9-1)/3)+1, which is INT(2.6667)+1, or 3. The INT version can make the quarter boundaries easier to see in a formula, while ROUNDUP is often easier to recognize at a glance. Choose either one and use it consistently in a workbook.
Both approaches assume A2 contains an Excel date value, not text that merely looks like a date. If the cell is blank, the formulas may treat its zero value as a date-related value and return a result that is not meaningful for your report. You can leave results blank for empty input by using an IF check.
With dates in A2 and downward, =IF(A2="","",ROUNDUP(MONTH(A2)/3,0)) returns an empty string when A2 is blank and the quarter number otherwise. The empty string looks blank but is technically a formula result, so functions that count non-empty cells may treat it differently from a truly empty cell.
Return a date for the start of the quarter
A quarter-start date is useful when you need chronological sorting, date-based filtering, or a genuine date axis in a chart. With a valid date in A2, use =DATE(YEAR(A2),3*ROUNDUP(MONTH(A2)/3,0)-2,1). The result is the first day of the quarter containing A2.
For example, if A2 is 15-May-2026, the formula returns 1-Apr-2026. If A2 is 2-Nov-2026, it returns 1-Oct-2026. The result is a real Excel date, even if the cell displays as a number. Apply a date format to make it readable: select the result cells, press Ctrl+1 on Windows to open Format Cells, and choose a date format. On a Mac, use Command+1 to open Format Cells.
You can display the start date as a quarter label without changing the underlying date value. Apply a custom format such as yyyy to show just the year, or retain a clear date format such as mmm yyyy to show the quarter’s starting month and year. A displayed format changes how the date appears, not its stored value. If a label like 2026 Q2 is required, use a separate text column or create the label from the date.
Calculate fiscal quarters
If a fiscal year starts in a month other than January, put its starting month number in E1: 1 for January, 4 for April, 7 for July, and so on. With the transaction date in A2 and the fiscal-year start month in E1, use =INT(MOD(MONTH(A2)-$E$1,12)/3)+1 to return the fiscal quarter. The dollar signs keep E1 fixed when you fill the formula down.
For example, if E1 contains 4 and A2 is 15-Nov-2026, the result is fiscal quarter 3: April through June is Q1, July through September is Q2, and October through December is Q3. If A2 is 10-Feb-2027, the same setup returns Q4. This formula labels quarters according to the chosen start month; it does not decide how your organization names its fiscal years.
Some organizations name a fiscal year for the calendar year in which it begins, while others use the year in which it ends. With a date in A2 and the fiscal-year start month in E1, =YEAR(A2)+(MONTH(A2)>=$E$1) returns the ending calendar year for a fiscal year that crosses into the next year. For an April start, a date in November 2026 returns 2027, and a date in February 2027 also returns 2027. If your organization names that same period FY2026 because it began in 2026, use the starting year convention instead and verify it against your reporting rules.
Check the month entered in E1: it must be a number from 1 through 12. A label such as Apr or April is not the numeric input this formula expects. Also confirm whether the fiscal calendar uses ordinary three-month quarters; some organizations use 4-4-5 or other retail calendars, which need a period table or a specifically designed calendar rather than this standard formula.
Use quarter fields to summarize data
For a small worksheet, a quarter label formula may be enough. For a larger sales or expense table, separate helper columns for Year and Quarter are often easier to audit and summarize. With dates in A2:A100, a year formula in B2 is =YEAR(A2), and a quarter formula in C2 is =ROUNDUP(MONTH(A2)/3,0). Fill both formulas down beside the records.
With years in B2:B100, quarter numbers in C2:C100, and sales amounts in D2:D100, use =SUMIFS($D$2:$D$100,$B$2:$B$100,2026,$C$2:$C$100,2) to total sales for calendar Q2 of 2026. For an illustrative dataset where those matching rows contain 5,000, 3,000, and 4,500 in sales, the formula returns 12,500. The dollar signs keep the data ranges fixed if you copy the formula elsewhere.
A PivotTable is another useful option when you want to explore many periods without writing a separate total formula for each one. If the source column contains valid dates, add the date field to a PivotTable and use its date-grouping options to group records by quarters and years. Grouping behavior can depend on the selected field and data; if dates are stored as text or the column contains blanks or errors, fix those entries first. Helper columns are often clearer when the source includes a custom fiscal calendar.
Common formula problems
- The result is wrong for a date that looks right. The cell may contain text rather than a real Excel date. Test a sample by changing its number format to General; a recognized date normally displays as a serial number. Convert text dates using a method appropriate for their source and regional date order before relying on MONTH or YEAR.
- The formula returns an error. Check that the referenced cell contains a valid date value and that the formula references the intended row. If dates are imported with inconsistent formats, some may be recognized and others may remain text.
- The year and quarter label sorts strangely. A combined label is text. Keep numeric year and quarter fields for sorting or use the quarter-start date formula to create a chronological date key.
- The fiscal quarter is off by one. Confirm that E1 holds the fiscal year’s first month as a number, not its last month, and that the organization uses three-month quarters beginning in that month.
- The formula shows a number instead of a date. A quarter-start formula returns a date serial. Apply a date number format; do not replace the value with text if you need date calculations or chronological sorting.
Excel’s argument separator is usually a comma in the formulas shown here, but installations configured for some regional settings may display or require semicolons instead. If Excel rejects an otherwise correct formula, check the list separator used by your regional settings. Function names and the underlying quarter logic remain the same.
Frequently Asked Questions
What is the formula to get the quarter from a date in Excel?
With a valid date in A2, use =ROUNDUP(MONTH(A2)/3,0). It returns a number from 1 to 4 for the calendar quarter.
How do I show the year and quarter together?
With a valid date in A2, use =YEAR(A2)&" Q"&ROUNDUP(MONTH(A2)/3,0). A date in May 2026 produces the text label 2026 Q2.
Can Excel calculate fiscal quarters?
Yes. Enter the fiscal year’s starting month as a number in E1, then use =INT(MOD(MONTH(A2)-$E$1,12)/3)+1 with the date in A2. Confirm how your organization defines and names its fiscal year.
How do I return the first date of a quarter?
With a valid date in A2, use =DATE(YEAR(A2),3*ROUNDUP(MONTH(A2)/3,0)-2,1). It returns January 1, April 1, July 1, or October 1 for calendar quarters.
Why does my quarter formula return the wrong answer?
Check that the source is a real Excel date, not text, and that you are using calendar quarters rather than a fiscal calendar. For fiscal quarters, the starting month must be set correctly.
Choosing the right approach
Use a numeric quarter formula when the result will drive calculations, filters, or summaries. Use a combined year-quarter label when a readable report heading is the main goal, and use a quarter-start date when you need a sortable date value. For fiscal reporting, explicitly define the start month and year-naming convention before building formulas. Keeping those choices separate makes the workbook easier to check and helps prevent calendar-quarter results from being mistaken for fiscal-quarter results.
Related
- Replace Excel Formatting: Find and Change Cell Styles
- Replace Data in Excel: Find and Replace Guide
- Excel Data Analysis: What It Can Do and Where It Falls Short
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Range Examples: Text, Numbers, and DatesUse Excel’s COUNTIF function to count cells in a range that match one condition: for example, =COUNTIF(A2:A20,"East") counts…
- HLOOKUP in Excel: Purpose, Examples, and LimitsHLOOKUP in Excel searches for a value across the top row of a table and returns a related…
- Excel Sort Dates From Earliest to Latest: A Clear GuideTo sort dates in Excel from earliest to latest, select a cell in your date column, then choose…
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare…
- Excel Extend Number Sequence: Fill Series and FormulasTo extend a number sequence in Excel, enter its first two values, select both cells, then drag the…
- Excel MIN Function Examples: Find the Smallest ValueExcel’s MIN function returns the smallest number in a set of values. For example, with scores in B2:B8,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.