The formula
Fiscal quarter (fill down)ExcelGoogle Sheets
=INT(MOD(MONTH(A2)-$B$2,12)/3)+1In D2 → 1
Fiscal year (named by the year it ends) (fill down)ExcelGoogle Sheets
=YEAR(EDATE(A2,MOD(13-$B$2,12)))In E2 → 2027
Label like "FY2027 Q3" (fill down)ExcelGoogle Sheets
="FY"&YEAR(EDATE(A2,MOD(13-$B$2,12)))&" Q"&(INT(MOD(MONTH(A2)-$B$2,12)/3)+1)In F2 → FY2027 Q1
Calendar quarter (fill down)ExcelGoogle Sheets
=ROUNDUP(MONTH(A2)/3,0)In G2 → 2
✓ Tested. All 16 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | FY start month | |||||
| 2 | Apr 1, 2026 | 4 | 1 | 2027 | FY2027 Q1 | 2 | |
| 3 | Jun 30, 2026 | 1 | 2027 | FY2027 Q1 | 2 | ||
| 4 | Dec 15, 2026 | 3 | 2027 | FY2027 Q3 | 4 | ||
| 5 | Mar 31, 2027 | 4 | 2027 | FY2027 Q4 | 1 |
How it works
- $B$2
- First month of the fiscal year (4 = April, 7 = July, 10 = October, 1 = calendar year).
- MOD(MONTH(A2)-$B$2,12)
- Months since the fiscal year started (0–11).
- INT(…/3)+1
- Groups those months into quarters 1–4.
- EDATE(A2,MOD(13-$B$2,12))
- Moves the date forward so the calendar year equals the fiscal year it ends in.
Common errors and fixes
Fiscal year off by one?
Some companies name the fiscal year by the year it starts. Use =YEAR(EDATE(A2,1-$B$2)) for that convention.
Why does the formula return #VALUE!?
The date is text.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.