Excel Find Quarter from Date: 5 Easy Formulas

To find the quarter from a date in Excel, use the formula =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). This article explains these and three other formulas, how to handle fiscal years, and common pitfalls.

Formula

The core formula is quarter = ROUNDUP(month / 3, 0).

• month – the month number of the date, from 1 (January) to 12 (December), extracted with the MONTH() function.
• ROUNDUP(x, 0) – rounds a number up to the nearest integer. Dividing the month by 3 gives: Jan–Mar → 0.33–1.0, Apr–Jun → 1.33–2.0, Jul–Sep → 2.33–3.0, Oct–Dec → 3.33–4.0. Rounding up maps each group to 1, 2, 3, or 4.

For a label like “Q1”, concatenate the letter Q with the result using the ampersand (&).

Worked Example

Using the default date 2024-05-14 (May 14, 2024):

1. Extract the month: MONTH("2024-05-14") → 5.
2. Divide by 3: 5 / 3 = 1.6667.
3. Round up to the nearest whole number: ROUNDUP(1.6667, 0) → 2.
4. The quarter number is 2, and the label is Q2.

This matches the calculator output above.

Why Use ROUNDUP Instead of INT or CEILING?

Many users try =INT(MONTH(A1)/3), but that gives 0 for January–March because INT truncates the decimal. ROUNDUP is the simplest fix. CEILING(month/3, 1) also works, but it is longer and less intuitive. The ROUNDUP approach is the standard in most Excel tutorials.

How to Handle Fiscal Years

If your company’s fiscal year starts in a month other than January, adjust the formula. For a fiscal year starting in April (month 4), the quarter is calculated as:

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

Here, MOD(month-3, 12) shifts the year so that April becomes month 1. For example, May (month 5) becomes 2, which divided by 3 and rounded up gives 1 – the first quarter of the fiscal year. If your fiscal year starts in July, subtract 6 instead of 3.

Common Mistakes and How to Avoid Them

• Using INT instead of ROUNDUP – yields 0 for Q1. Always use ROUNDUP.
• Forgetting to reference the correct cell – double-check that A1 contains a real date, not text. Text dates can cause #VALUE! errors.
• Not accounting for fiscal year start – if your fiscal year is not January, the simple formula gives the calendar quarter, not the fiscal quarter.
• Mixing date formats – Excel stores dates as serial numbers; the MONTH function works regardless of display format, but if you type a date as text, it may not be recognized.
• Expecting a text result but getting a number – if you want “Q1”, you must concatenate with the ampersand. Otherwise you get 1.

Alternative Formulas for Specific Needs

• Quarter with year: ="Q"&ROUNDUP(MONTH(A1)/3,0)&"-"&YEAR(A1) → “Q2-2024”.
• Quarter start date: =DATE(YEAR(A1), (ROUNDUP(MONTH(A1)/3,0)-1)*3+1, 1) → first day of the quarter.
• Quarter end date: =EOMONTH(DATE(YEAR(A1), ROUNDUP(MONTH(A1)/3,0)*3, 1), 0) → last day of the quarter.
• Using CHOOSE: =CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4) – a longer but explicit alternative.

When to Use This Formula

Use it for financial reports, sales analysis, performance reviews, or any data that is grouped by calendar quarter. It works with any date in Excel, including dates from other formulas. The result is a number (or text with the Q), which can be used in pivot tables, charts, or conditional formatting.

Limitations and Notes

The formula assumes a standard calendar year (January–December). It does not handle fiscal years that start in other months unless you modify it as shown above. It also does not account for ISO week-based quarters, which are rarely used in business. For most purposes, the simple ROUNDUP approach is sufficient.

Frequently Asked Questions

How do I get the quarter as “Q1” instead of just 1?

Use ="Q"&ROUNDUP(MONTH(A1)/3,0). The ampersand joins the letter Q with the number. If you want a space, use ="Q "&....

What if my date is in a text format like “2024-05-14”?

Excel may not recognize it as a date. Use DATEVALUE() to convert it, or ensure the cell is formatted as a date. For example, =ROUNDUP(MONTH(DATEVALUE(A1))/3,0).

Can I use this in Google Sheets?

Yes, the same formulas work in Google Sheets. The functions MONTH, ROUNDUP, and DATE are identical.

How do I calculate the quarter for a fiscal year that starts in October?

Subtract 9 from the month: =ROUNDUP(MOD(MONTH(A1)-9,12)/3,0). October becomes month 1, November month 2, etc.

Why does my formula return #VALUE!?

That usually means the cell does not contain a valid date. Check that the cell is formatted as a date and contains a real date value, not text. Use =ISDATE(A1) to test.

Is there a way to get the quarter without a formula?

Yes, you can use a pivot table with the date field grouped by quarter, but that requires manual grouping. The formula is faster and updates automatically.

Related

• Remove Duplicate Files from Google Drive
• Division 2 Dust Storm: All 20 Hidden Caches
• Digital Nomad Lifestyle: 63 Free Posts to Learn

in d | Date | - | 2024-05-14 | 0
out quarter | Quarter number | - | roundup(month(d)/3, 0) | 0
out quarter_label | Quarter label | - | "Q" & roundup(month(d)/3, 0) | 0