# How to Calculate Quarter of Year in Excel (Step by Step)

The quarter of year is the three-month block that a date falls into, and Excel has no built-in quarter function. You can get it with a single formula that reads the month number and rounds it up to the next multiple of three. This article shows the exact formula, how to build a Q1 to Q4 label, and how to adapt it for fiscal years.

## Quick Answer

- Quarter number: `=ROUNDUP(MONTH(A2)/3,0)` returns 1, 2, 3, or 4 for the date in A2.
- Quarter label: `="Q"&ROUNDUP(MONTH(A2)/3,0)` returns Q1, Q2, Q3, or Q4.
- The logic: January to March is months 1 to 3, so dividing by 3 and rounding up gives 1. April to June gives 2, and so on.
- A calendar year splits into four quarters: Q1 is January to March, Q2 is April to June, Q3 is July to September, and Q4 is October to December [1].
- If your year starts in a month other than January, shift the month number before rounding, as shown in Other Ways to Do It.

## Before You Start

You need one thing: a column of real dates. Excel stores dates as serial numbers, so `MONTH` can read them directly. If your "dates" are text in a format Excel cannot read as a date, `MONTH` returns a `#VALUE!` error. You can test this by checking whether the value is right-aligned in the cell. Real dates align right by default, text aligns left.

The `MONTH` function takes a date and returns an integer from 1 to 12. The `ROUNDUP` function takes a number and a digit count and rounds away from zero. With a digit count of 0, `ROUNDUP(2.33,0)` returns 3. That combination is the whole trick.

If you are new to writing formulas, the mechanics of cell references and operators are covered in [how to make a formula in Excel](/blog/data-analysis/how-to-make-formula-in-excel).

## Step by Step

1. Put your dates in column A, starting at A2. Add headers in row 1: Date, Quarter Number, Quarter Label.
2. In B2, extract the month and convert it to a quarter:
   ```
   =ROUNDUP(MONTH(A2)/3,0)
   ```
3. In C2, build the label by joining the letter Q to the number:
   ```
   ="Q"&ROUNDUP(MONTH(A2)/3,0)
   ```
4. Select B2:C2 and drag the fill handle down to the last row of dates. Excel adjusts the row references automatically.
5. Check the results against the month. Any date in January, February, or March must show 1 and Q1.

The math behind step 2 is:

$$
\text{Quarter} = \left\lceil \frac{\text{Month}}{3} \right\rceil
$$

`ROUNDUP` with a digit count of 0 is Excel's ceiling function for positive numbers, which is why it works here.

## Worked Example

The table below uses a small sales log with eight order dates spread across 2024 and 2025. Column A holds the dates, column B the quarter number, and column C the quarter label.

| Row | A | B | C |
|---|---|---|---|
| 1 | Date | Quarter Number | Quarter Label |
| 2 | `=DATE(2024,1,15)` -> displays 01/15/2024 | `=ROUNDUP(MONTH(A2)/3,0)` -> displays 1 | `="Q"&ROUNDUP(MONTH(A2)/3,0)` -> displays Q1 |
| 3 | `=DATE(2024,4,1)` -> displays 04/01/2024 | `=ROUNDUP(MONTH(A3)/3,0)` -> displays 2 | `="Q"&ROUNDUP(MONTH(A3)/3,0)` -> displays Q2 |
| 4 | `=DATE(2024,7,20)` -> displays 07/20/2024 | `=ROUNDUP(MONTH(A4)/3,0)` -> displays 3 | `="Q"&ROUNDUP(MONTH(A4)/3,0)` -> displays Q3 |
| 5 | `=DATE(2024,10,5)` -> displays 10/05/2024 | `=ROUNDUP(MONTH(A5)/3,0)` -> displays 4 | `="Q"&ROUNDUP(MONTH(A5)/3,0)` -> displays Q4 |
| 6 | `=DATE(2025,2,14)` -> displays 02/14/2025 | `=ROUNDUP(MONTH(A6)/3,0)` -> displays 1 | `="Q"&ROUNDUP(MONTH(A6)/3,0)` -> displays Q1 |
| 7 | `=DATE(2025,5,30)` -> displays 05/30/2025 | `=ROUNDUP(MONTH(A7)/3,0)` -> displays 2 | `="Q"&ROUNDUP(MONTH(A7)/3,0)` -> displays Q2 |
| 8 | `=DATE(2025,8,9)` -> displays 08/09/2025 | `=ROUNDUP(MONTH(A8)/3,0)` -> displays 3 | `="Q"&ROUNDUP(MONTH(A8)/3,0)` -> displays Q3 |
| 9 | `=DATE(2025,11,23)` -> displays 11/23/2025 | `=ROUNDUP(MONTH(A9)/3,0)` -> displays 4 | `="Q"&ROUNDUP(MONTH(A9)/3,0)` -> displays Q4 |

Row 2 walks through the logic. `MONTH(A2)` returns 1. Dividing by 3 gives 0.333. `ROUNDUP` pushes that to 1, so the quarter is 1 and the label is Q1. Row 5 shows the other end of the scale: `MONTH(A5)` returns 10, dividing by 3 gives 3.333, and `ROUNDUP` returns 4.

Once column B exists, you can group and summarize by quarter. A pivot table or a chart built on the quarter column turns a flat date list into a quarterly trend. The steps for that are in [how to make a chart in Excel](/blog/data-analysis/how-to-make-chart-in-excel).

## Other Ways to Do It

**Fiscal quarters.** Many organizations do not use the calendar year. UC Irvine, for example, runs its fiscal year from July 1 to June 30, so Fiscal Year 2027 covers July 1, 2026 through June 30, 2027 [2]. If your fiscal year starts in month $s$, shift the month before rounding:

$$
\text{Fiscal Quarter} = \left\lceil \frac{\text{MOD}(\text{Month} - s, 12) + 1}{3} \right\rceil
$$

For a July start, $s = 7$. A date in July gives `MOD(7-7,12)+1 = 1`, so it lands in fiscal Q1. A date in June gives `MOD(6-7,12)+1 = 12`, so it lands in fiscal Q4. In Excel for a July start:

```
=ROUNDUP((MOD(MONTH(A2)-7,12)+1)/3,0)
```

**Quarter start date.** To return the first day of the quarter instead of a number, use `=DATE(YEAR(A2),ROUNDUP(MONTH(A2)/3,0)*3-2,1)`.

**Quarter with the year.** To keep periods distinct across years, use `="Q"&ROUNDUP(MONTH(A2)/3,0)&"-"&YEAR(A2)`, which returns labels like Q1-2024.

**Text month names.** If you only have a month name, convert it to a number first with `=MONTH(DATEVALUE(A2&" 1"))`, then apply the same rounding.

## Troubleshooting

| Symptom | Likely cause | Fix |
|---|---|---|
| `#VALUE!` in the quarter column | The date cell contains text, not a date | Retype the value as a date or convert it with `DATEVALUE` |
| All results show 1 | The formula references the same cell in every row | Remove the `$` from the row reference or re-drag the fill handle |
| Quarter looks right but the label shows a number | The `"Q"` text was typed without quotes | Wrap the letter in double quotes |
| Results are off by one for fiscal data | The formula assumes a January start | Shift the month with `MOD` as shown above |
| `#NAME?` error | `ROUNDUP` is misspelled or the function name is in another language | Retype the function name in your Excel language |

## Common Mistakes

- **Using `ROUND` instead of `ROUNDUP`.** `ROUND(1/3,0)` returns 0, which is not a valid quarter. Use `ROUNDUP` so any fraction rounds up to the next whole quarter.
- **Dividing by 4.** A quarter covers three months, not four. Dividing the month by 4 gives wrong answers for most dates.
- **Forgetting the digit argument.** `ROUNDUP(MONTH(A2)/3)` does not work, because ROUNDUP has no default for num_digits and Excel rejects the formula for having too few arguments. Always write the 0.
- **Treating a quarter label as a number.** Q1 is text. If you sort it as text, Q10 would appear before Q2 in a larger scheme, and numeric comparisons fail. Keep the quarter number in its own column for sorting and math.
- **Assuming every quarter has the same number of days.** Q1 has 90 days, or 91 in a leap year, Q2 has 91, and Q3 and Q4 each have 92 [1]. Averages per day will differ slightly between quarters.
- **Mixing calendar and fiscal quarters in one report.** Pick one definition and apply it to every row. A July date is Q3 on a calendar basis and Q1 on a July-start fiscal basis.

## Limitations

This method returns the quarter that a date falls into. It does not tell you which quarter a transaction belongs to under accrual accounting rules, where revenue may be recognized in a period different from the invoice date. For that you need your organization's period definitions, not a date formula.

The formula also assumes fixed three-month quarters. Some reporting groups use quarters of exactly 13 weeks, which follow ISO week conventions, and roughly one year in five or six has a 53rd week that is usually appended to the last quarter, making it 98 days instead of 91 [1]. A month-based formula cannot reproduce those boundaries. If your reporting calendar uses 4-4-5 weeks or 13-week quarters, you need a lookup table that maps each date range to its period.

## Frequently Asked Questions

### What is the formula for the quarter of year in Excel?

Use `=ROUNDUP(MONTH(A2)/3,0)` for the quarter number and `="Q"&ROUNDUP(MONTH(A2)/3,0)` for the label. Both read the month from the date and round it up to the next multiple of three. There is no dedicated quarter function in Excel, so this two-function combination is the standard approach.

### How do I convert a year to quarter in Excel?

If you have a year value and want to list its four quarters, generate the quarter start dates with `=DATE(year, (n-1)*3+1, 1)` where n runs from 1 to 4. That gives January 1, April 1, July 1, and October 1 of that year. From there you can label each row Q1 through Q4.

### How do I calculate fiscal quarters in Excel?

Shift the month number so your fiscal year start becomes month 1, then apply the same rounding. For a July start, use `=ROUNDUP((MOD(MONTH(A2)-7,12)+1)/3,0)`. Change the 7 to your own start month. This keeps the quarter numbering aligned to your fiscal calendar instead of the calendar year [2].

### Why does my quarter formula return the wrong number?

The most common cause is a date stored as text, which makes `MONTH` fail or return an unexpected value. Another cause is a mixed-up cell reference after copying the formula. Check that the cell contains a real date and that the row reference changes as you fill down.

### Can I get the quarter as a date range instead of a number?

Yes. The quarter start is `=DATE(YEAR(A2),ROUNDUP(MONTH(A2)/3,0)*3-2,1)`. The quarter end is the day before the next quarter start, which you can get with `=DATE(YEAR(A2),ROUNDUP(MONTH(A2)/3,0)*3+1,1)-1`. These two formulas give you a clean period boundary for reporting.

If you also work with durations and deadlines, the same date logic appears in [how to calculate age in Excel](/blog/data-analysis/how-to-calculate-age-in-excel) and [how to calculate working days in a year in Excel](/blog/data-analysis/calculate-working-days-in-a-year-excel).

## References

1. [Calendar year - Wikipedia](https://en.wikipedia.org/wiki/Calendar_year)
2. [Understanding Fiscal Years and Fiscal Periods // Accounting & Fiscal Services // UC Irvine](https://www.accounting.uci.edu/support/fiscal-officers/general-ledger/fiscal-period.php)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)

## Related Articles

- [How to Calculate Age in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-age-in-excel)
- [How to Calculate Working Days in a Year in Excel (NETWORKDAYS)](/blog/data-analysis/calculate-working-days-in-a-year-excel)
- [How to Calculate the Mean in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-mean-in-excel)
- [How to Find Percentage in Excel (Step by Step)](/blog/data-analysis/how-to-find-percentage-in-excel)
- [How to Calculate Variance in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-variance-in-excel)