For an April to March year, the quarter of a date in A2 is ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0). It counts months from April, divides by three and rounds up, so April to June gives Q1, July to September Q2, October to December Q3 and January to March Q4. The same formula works in Excel and in Google Sheets.
How does the quarter formula work?
There are three small moves inside it. MONTH(A2) returns 1 to 12 for January to December. Subtracting 4 and wrapping with MOD(...,12) turns that into a position in the financial year: MOD(MONTH(A2)-4,12)+1 gives April as 1, May as 2 and so on up to March as 12. Both Excel and Sheets give MOD the sign of the divisor, so January (1 minus 4 is -3) wraps round to 9, and adding 1 makes it 10. Finally ROUNDUP(.../3,0) lifts 1 to 3 into quarter 1, 4 to 6 into quarter 2, and so on.
Here are three dates traced by hand, so you can test your own sheet:
- 14 August 2025: MONTH is 8, 8-4 is 4, MOD gives 4, plus 1 is 5, 5/3 is 1.67, ROUNDUP gives 2. Result: Q2.
- 9 January 2026: MONTH is 1, 1-4 is -3, MOD gives 9, plus 1 is 10, 10/3 is 3.33, ROUNDUP gives 4. Result: Q4.
- 30 June 2025: MONTH is 6, 6-4 is 2, plus 1 is 3, 3/3 is exactly 1. Result: Q1.
Which months fall in which quarter?
| Quarter | Months | Fiscal month number |
|---|---|---|
| Q1 | April, May, June | 1, 2, 3 |
| Q2 | July, August, September | 4, 5, 6 |
| Q3 | October, November, December | 7, 8, 9 |
| Q4 | January, February, March | 10, 11, 12 |
If you only ever want the fiscal month, use =MOD(MONTH(A2)-4,12)+1 on its own. It is also handy for sorting a summary so April comes first and March last.
Do I need a financial year column as well?
Yes, as soon as your log covers more than one year. Q1 of 2025-26 and Q1 of 2026-27 would both say Q1, and a quarterly total would quietly mix them. Add a column that holds the year in which the financial year started. Say the log has Date in A, Category in B and Amount in C. Then:
- D2 (starting year): =IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1)
- E2 (quarter label): ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0)
A date of 9 January 2026 gives 2025 in column D, which is the starting year of 2025-26. Fill both formulas down, or in Excel 365 and Google Sheets use the array versions described below.
How do I build the Q1 to Q4 summary table?
Type the starting year you want in H1 (for example 2025) and the labels Q1, Q2, Q3, Q4 across G3:J3. Then in G4 write:
=SUMIFS($C$2:$C$500,$E$2:$E$500,G$3,$D$2:$D$500,$H$1)
Fill it across to J4. SUMIFS takes the amounts to add first, then each range and criterion in pairs. Put =SUM(G4:J4) in K4 and compare it with =SUMIFS($C$2:$C$500,$D$2:$D$500,$H$1); the two must match, otherwise some row has a blank or mistyped date. As a worked example, say these hypothetical rows sit in the 2025-26 log:
| Date | Amount (₹) | Quarter |
|---|---|---|
| 30 June 2025 | 1,500 | Q1 |
| 14 August 2025 | 2,400 | Q2 |
| 22 September 2025 | 1,100 | Q2 |
| 5 November 2025 | 3,000 | Q3 |
| 9 January 2026 | 700 | Q4 |
The summary row reads Q1 ₹1,500, Q2 ₹3,500 (2,400 plus 1,100), Q3 ₹3,000, Q4 ₹700, and the total is ₹8,700.
Can the column fill itself in Sheets or Excel 365?
In Google Sheets, put this in E2 and leave the cells below it empty: =ARRAYFORMULA(IF(A2:A="","","Q"&ROUNDUP((MOD(MONTH(A2:A)-4,12)+1)/3,0))). In Excel 365 or 2021, =IF(A2:A500="","","Q"&ROUNDUP((MOD(MONTH(A2:A500)-4,12)+1)/3,0)) spills down by itself. Older Excel versions need the normal formula filled down.
A quick check before you rely on it
- Type four test dates: 31 March, 1 April, 30 June and 1 July. You should see Q4, Q1, Q1 and Q2.
- Confirm the four quarterly totals add up to the full-year total.
- Remember this only labels dates. Return and payment due dates follow tax rules, so check those with your CA or the official source and not from this sheet.
When typing every row by hand becomes the real chore, SpendixAI can take a bank or card statement uploaded as PDF, CSV or Excel, let you review the rows before saving, and offer report data as a CSV download that you can drop back into this same quarter sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


