Get the quarter from a date
PremiumIn B2, find the quarter of the sale date in A2, a number from 1 to 4. Divide the month by 3 and round up: ROUNDUP(MONTH(A2)/3,0).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sale date | Quarter | ||||
| 2 | Aug 20, 2024 | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Dates and DATE and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for finance jobs
Why this works
MONTH(A2) gives 8 for August, and 8 / 3 = 2.67. Rounding up gives 3. Every month in a quarter lands on the same number: July, August and September give 2.33, 2.67 and 3, which all round up to 3.
Common mistake: Using ROUND. It sends the first month of each quarter, like July at 2.33, back to the quarter before.
The answer
- B2
=ROUNDUP(1MONTH(A2)/3,20)
In plain words: Divide 1the month of the date in A2 by 3 and round up to 2a whole number.