Cellwise Try it free

Excel guide · 3 min read

Get the quarter from a date in Excel

Quick answerUse =ROUNDUP(MONTH(A2)/3,0) to get the quarter number 1 to 4, or ="Q"&ROUNDUP(MONTH(A2)/3,0) for a label like Q2. Months 1-3 give Q1, 4-6 give Q2 and so on. Excel has no QUARTER function and no quarter code in TEXT, so you build it from the month.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1Invoice dateAmountFiscal year starts (month)
2Jan 14, 202642007
3Mar 31, 20263150
4Apr 2, 20265800
5Jun 18, 20262475
6Jul 1, 20266100
7Aug 22, 20263900
8Oct 5, 20264650
9Dec 30, 20265320

Download this sheet as an Excel practice file (.xlsx)

Calendar quarter from the month

MONTH returns 1 to 12. Dividing by 3 gives 0.33 to 4, and ROUNDUP lifts each value to the next whole number: January to March become 1, April to June become 2. The invoice dated April 2 lands in Q2 while March 31 stays in Q1.

=INT((MONTH(A2)+2)/3) gives the same numbers if you prefer it, but ROUNDUP is easier to explain to the next person.

=ROUNDUP(MONTH(A4)/3,0)

Result: 2

2: April is month 4, and 4/3 rounds up to 2.

Add the year to the label

Q4 alone is ambiguous once a sheet covers more than one year. Join the year in front. The year-first format, 2026-Q4, also sorts correctly as text, which "Q4 2026" does not.

=YEAR(A8)&"-Q"&ROUNDUP(MONTH(A8)/3,0)

Result: 2026-Q4

2026-Q4 for the October 5 invoice.

Fiscal quarters (year starting in July)

Many companies, schools and governments run a fiscal year that does not start in January. Put the start month in a cell (7 for July here). MOD(MONTH(A2)-D2,12) counts the months since the fiscal year started, wrapping around December, and dividing by 3 turns that into a quarter.

The fiscal year is often named after the calendar year it ends in, so July 2026 to June 2027 is FY2027. =YEAR(A2)+(MONTH(A2)>=D2) gives that, for any start month from February to December. Conventions vary, so check what your finance team calls it.

="FY"&(YEAR(A6)+(MONTH(A6)>=D2))&" Q"&(INT(MOD(MONTH(A6)-D2,12)/3)+1)

Result: FY2027 Q1

FY2027 Q1: July 1, 2026 is the first day of the fiscal year that ends in June 2027.

First and last day of the quarter

For deadlines and reports you often need the quarter's boundaries. The first month of the quarter is (quarter-1)*3+1, so DATE builds the start date. For the end date, EOMONTH jumps forward to the last month of the quarter: MOD(3-MONTH(A7),3) is the number of months left after the date's own month.

The start date is =DATE(YEAR(A7),(ROUNDUP(MONTH(A7)/3,0)-1)*3+1,1), which returns July 1, 2026 for the August invoice. Subtract the date from the end date to get the days left in the quarter: 39 here.

=EOMONTH(A7,MOD(3-MONTH(A7),3))

Result: Sep 30, 2026

Sep 30, 2026: the last day of the quarter that contains August 22.

Sum by quarter

With a helper column of quarter labels, SUMIF does the job. Without one, SUMPRODUCT can calculate the quarter for every row on the fly. This only looks at the month, so use it on a single year of data, or add (YEAR(A2:A9)=2026) as another condition.

For several years, SUMIFS with the quarter's first and last day is safer: =SUMIFS(B2:B9,A2:A9,">="&DATE(2026,4,1),A2:A9,"<"&DATE(2026,7,1)) gives the same 8,275. A PivotTable can do it with no formula: right-click a date in the Rows area, choose Group, and select Quarters and Years.

=SUMPRODUCT((ROUNDUP(MONTH(A2:A9)/3,0)=2)*B2:B9)

Result: 8275

8,275: the two invoices from April to June (5,800 and 2,475).

Common mistakes

Using TEXT(A2,"q")

TEXT has no quarter code. Calculate it with ROUNDUP(MONTH(A2)/3,0).

Rounding with ROUND instead of ROUNDUP

ROUND(4/3,0) is 1, which puts April in Q1. Use ROUNDUP, or INT((MONTH+2)/3).

Mixing up years in quarter totals

Q2 2025 and Q2 2026 both have quarter 2. Add the year to the label or the condition.

Dates stored as text

MONTH can return the wrong month or #VALUE! for text dates, depending on regional settings. Convert them to real dates with Data > Text to Columns.

FAQ

Is there a QUARTER function in Excel?

No. Use =ROUNDUP(MONTH(A2)/3,0) for the quarter number, or ="Q"&ROUNDUP(MONTH(A2)/3,0) for a label.

How do I calculate a fiscal quarter in Excel?

With the fiscal start month in D2, use =INT(MOD(MONTH(A2)-D2,12)/3)+1. For a July start, July to September is Q1.

How do I get the quarter and year in one cell?

=YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0) returns labels like 2026-Q3 that sort in date order.

How do I find the end of the quarter for a date?

=EOMONTH(A2,MOD(3-MONTH(A2),3)) returns the last day of the quarter that contains A2.

Functions used

Related guides

Updated