Excel Formula for Quarter From Date: 3 Easy Methods

Formulas & functions · Updated November 18, 2026

If the date is in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from 1 to 4. To display a label such as Q2 instead, join the letter Q to that result.

How the calendar-quarter formula works

Excel stores dates as serial numbers, but MONTH extracts the month number from a valid date: January is 1, February is 2, and December is 12. Dividing the month by 3 and rounding up groups those twelve months into four sets. The formula gives 1 for January through March, 2 for April through June, 3 for July through September, and 4 for October through December.

For example, if A2 contains May 15, 2025, MONTH(A2) returns 5. Five divided by 3 is about 1.67, which ROUNDUP rounds to 2. The formula therefore returns 2, meaning the date is in the second calendar quarter. The result is a number, not text, which is useful if you want to sort, filter, or compare quarter numbers later.

To fill the result down a table, enter the formula in the first result cell next to the date and copy it to the other rows. In a structured Excel table, entering the formula in a column usually fills the rest of that calculated column automatically. The reference A2 adjusts to A3, A4, and so on as the formula is copied.

Return a Q1–Q4 label

If a report should show Q1 rather than the number 1, add a text prefix. With the date in A2, use ="Q"&ROUNDUP(MONTH(A2)/3,0). For May 15, 2025, this returns the text Q2. The ampersand joins text and a calculated value; it does not change the date or the underlying quarter boundaries.

This is convenient for headings, charts, and exported reports. However, the result is text. If you sort labels alphabetically, Q1, Q2, Q3, and Q4 happen to appear in the expected order, but labels that also contain a year can sort unexpectedly. For example, text values beginning with 2024 and 2025 may sort as text rather than as dates. If you need reliable chronology across multiple years, keep the date or numeric year and quarter in separate columns, or use a true quarter-start date.

A number format can change how a cell looks, but it cannot automatically turn an ordinary date into a quarter label while retaining that date as its displayed value. A formula is the straightforward option when the cell must show Q1–Q4.

Add the year to the quarter

Reports often need to distinguish the second quarter of one year from the second quarter of another. With the date in A2, use =YEAR(A2)&" Q"&ROUNDUP(MONTH(A2)/3,0). If A2 contains May 15, 2025, the result is 2025 Q2. YEAR supplies the calendar year, while the quarter calculation supplies the quarter number.

This combined result is readable, but it is still text. If you plan to calculate with the year and quarter, retain them as separate numeric fields: YEAR(A2) for the year and the basic quarter formula for the quarter number. Then sort by year first and quarter second. This avoids relying on text sorting and makes it easier to filter for, say, quarter 2 across several years.

Another useful reporting value is the first day of the quarter. With the date in A2, use =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1). For May 15, 2025, this returns April 1, 2025. A real date can be formatted as needed and used in date comparisons, charts, or grouped summaries. The formula identifies the quarter’s starting month and DATE constructs the first day of that month.

Use fiscal quarters instead

Calendar quarters begin in January, but a company, school, or government may use a fiscal year that begins in another month. The calendar-quarter formula will not match that schedule. For a fiscal year beginning in July, January is in fiscal Q3, not calendar Q1.

For a flexible fiscal-quarter calculation, put the fiscal year’s starting month number in B1 and the date in A2. For example, enter 7 in B1 for July. Then use =INT(MOD(MONTH(A2)-$B$1,12)/3)+1. With A2 set to January 15, 2025, and B1 set to 7, the result is 3: January falls in the third fiscal quarter of a July-start year. The dollar signs keep the B1 reference fixed when you copy the formula down.

The B1 value should be an integer from 1 to 12: 1 for January, 2 for February, and so on. The formula measures how many months have passed since the fiscal start, wraps the result into a twelve-month cycle, divides that cycle into groups of three, and adds 1 because quarter numbering begins at 1. It returns the quarter number only; it does not decide how your organization names the fiscal year. Some organizations name a fiscal year for the year in which it starts, while others use the year in which it ends, so confirm the naming rule before building a fiscal year label.

Choose the right method for your report

The best result depends on what you will do with the quarter after calculating it. These common choices serve different purposes:

  • Quarter number: Use the basic formula when you need a numeric value from 1 to 4 for filtering, calculations, or grouping.
  • Q1–Q4 label: Use the text-prefix version for a compact display in a report or chart.
  • Year and quarter: Combine YEAR with the quarter when readers need a label that separates different years.
  • Quarter-start date: Return the first day of the quarter when later steps involve date ranges or chronological sorting.
  • Fiscal quarter: Use a start-month calculation when the reporting year does not begin in January.

For analysis, it is often sensible to preserve the original date and add a separate quarter column rather than replace the date. The date remains the detailed record of when an event happened; the quarter is a derived grouping. Keeping both lets you change the report’s grouping later without losing the original information.

Common problems and checks

If Excel returns an error or a quarter that does not make sense, first check whether the source value is a genuine Excel date. A cell can look like a date while containing text. MONTH expects a date value; text imported from another system may not be recognized consistently, particularly when the day and month order is ambiguous. Select the cell and check whether Excel treats it as a date, then convert or re-import text dates using a method that matches the source format. Simply applying a date display format to text does not necessarily convert it.

Blank cells can also produce misleading results because Excel may treat an empty reference like a zero in some date calculations. If the source column contains blanks, use a guarded version. With dates in A2 and quarter results in B2, use =IF(A2="","",ROUNDUP(MONTH(A2)/3,0)). It leaves B2 blank when A2 is blank and calculates the quarter otherwise. This is useful in an unfinished list, though it does not validate every possible entry: a nonblank invalid value may still cause a problem.

Check the reporting definition as well as the arithmetic. A result of 1 for February is correct for calendar quarters, but may be wrong for an organization whose fiscal year begins in July. Also check whether a report calls the quarter by its start year or end year. The quarter number alone cannot settle those business rules.

Finally, remember that a quarter is a three-month calendar grouping, not necessarily a fixed 90-day period. Months have different lengths, and leap years affect February. Use month-based quarter logic rather than dividing a day-of-year number by 90; a day-count shortcut can put dates near quarter boundaries into the wrong group.

Alternatives for summaries and charts

A formula is a good fit when you want a reusable quarter column in a data table. For a one-time summary, a PivotTable can group a date field by quarters, provided the source values are recognized as dates and the grouping options are available for that field. Select a date in the PivotTable, right-click, choose Group, and select Quarters. To distinguish quarters across years, include Years in the grouping too; otherwise, all first quarters may appear together in a summary.

Formula columns can be easier to audit because the rule is visible beside each record, and they work well when the quarter is needed outside a PivotTable. PivotTable grouping can be quicker when the goal is an interactive summary that can be refreshed. Neither approach changes the underlying dates. If the source includes text dates, blanks, or inconsistent fiscal rules, fix or clarify those inputs before trusting the summary.

Frequently Asked Questions

What is the simplest Excel formula to get a quarter from a date?
With a valid date in A2, use =ROUNDUP(MONTH(A2)/3,0). It returns a number from 1 to 4 for calendar quarters.

How do I show Q1 instead of 1?
With the date in A2, use ="Q"&ROUNDUP(MONTH(A2)/3,0). It returns a text label such as Q1 or Q4.

How do I include the year in the quarter result?
With the date in A2, use =YEAR(A2)&" Q"&ROUNDUP(MONTH(A2)/3,0). A date in May 2025 returns 2025 Q2.

Can Excel calculate fiscal quarters?
Yes. Put the fiscal year’s starting month number in B1 and the date in A2, then use =INT(MOD(MONTH(A2)-$B$1,12)/3)+1. Set B1 to 7 for a July start.

Why does the formula return the wrong quarter?
Check whether the date is a real Excel date and whether you need fiscal rather than calendar quarters. Also verify the organization’s fiscal-year start and year-labeling convention.

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.