The simplest Excel formula to return the financial quarter from a date in A1 is =ROUNDUP(MONTH(A1)/3,0), which gives 1–4 based on a calendar year. If your fiscal year starts in a month other than January, use =MOD(CEILING(MONTH(A1)-FiscalStartMonth+1,3)/3,4)+1 (replace FiscalStartMonth with the month number your fiscal year begins, e.g., 4 for April). This article explains both formulas, how to customize them, and common pitfalls.
Why a Simple Quarter Formula Isn’t Always Enough
Many people first try =INT((MONTH(A1)+2)/3) or =ROUNDUP(MONTH(A1)/3,0). Both work for a standard calendar year (Jan–Mar = Q1, Apr–Jun = Q2, etc.). But businesses often run on a fiscal year that starts in April, July, or October. For example, the U.S. federal government’s fiscal year starts October 1, and many retailers use a February start. The formula must shift the quarter boundaries accordingly. Additionally, you may want to display the quarter as text like “Q1” or “FY25-Q1”, which requires a different approach.
How the Calendar Quarter Formula Works
The core idea is that each quarter spans three months. MONTH(A1) returns 1–12. Dividing by 3 gives 0.33, 0.67, 1.0, etc. ROUNDUP rounds up to the nearest integer, so month 1 gives 1, month 3 gives 1, month 4 gives 2, and month 12 gives 4. This works because every month number falls into a 3-month block. The alternative =INT((MONTH(A1)+2)/3) adds 2 before dividing, which effectively shifts the rounding down—both are valid, but ROUNDUP is more intuitive.
Example: with a date in A1 like 2024-05-14, MONTH(A1) returns 5, 5/3 = 1.6667, ROUNDUP(1.6667,0) = 2, so the quarter is 2. If you want a text label, use ="Q"&ROUNDUP(MONTH(A1)/3,0).
Handling a Non-Calendar Fiscal Year
When your fiscal year starts in month F (e.g., 4 for April), you need to shift the month numbers. The formula =MOD(CEILING(MONTH(A1)-F+1,3)/3,4)+1 does this. Here’s the logic: subtract the starting month minus 1 to align months so that the first fiscal month becomes 1. Then use CEILING to round up to the next multiple of 3, divide by 3 to get 1, 2, 3, or 4, and MOD with 4 to wrap around (so month 12 becomes quarter 4, not 5).
Example: fiscal year starts in April (F=4). Date 2024-03-15 (month 3) – 4 + 1 = 0, CEILING(0,3)=0, 0/3=0, MOD(0,4)+1=1? Actually that gives 1, but March is the last month of the fiscal year (Q4). Let’s test: month 3 – 4 + 1 = 0, CEILING(0,3) returns 0, 0/3=0, MOD(0,4)=0, +1 = 1. That’s wrong. The correct formula is =MOD(CEILING(MONTH(A1)-F+1,3)/3,4)+1 but with a tweak: for month 3, we want 4. The issue is that CEILING(0,3) returns 0, not 3. To fix, use =MOD(CEILING(MONTH(A1)-F,3)/3,4)+1. Let’s recalc: month 3, F=4: 3-4 = -1, CEILING(-1,3) returns -0? Actually CEILING(-1,3) returns 0 because -1 is rounded up to 0. Still 0. The standard formula that works is =MOD(CEILING(MONTH(A1)-F+1,3)/3,4)+1 but with a different approach: use =MOD(CEILING((MONTH(A1)-F+1)/3,1)-1,4)+1? That’s messy. A simpler, robust formula is =MOD(QUOTIENT(MONTH(A1)-F,3)+1,4)+1 but QUOTIENT truncates. Let’s step back.
The cleanest way is to create a lookup table or use a formula that directly computes the offset. For a fiscal year starting in month F, the quarter for a month M is: =MOD(CEILING((M-F+1)/3,1)-1,4)+1. Test: F=4, M=3: (3-4+1)=0, 0/3=0, CEILING(0,1)=0, 0-1=-1, MOD(-1,4)=3, +1=4. Correct. M=4: (4-4+1)=1, 1/3=0.333, CEILING=1, 1-1=0, MOD(0,4)=0, +1=1. Correct. M=5: (5-4+1)=2, 2/3=0.667, CEILING=1, 0, +1=1. Correct. M=6: (6-4+1)=3, 3/3=1, CEILING=1, 0, +1=1. Correct. M=7: (7-4+1)=4, 4/3=1.333, CEILING=2, 2-1=1, +1=2. Correct. So the formula is =MOD(CEILING((MONTH(A1)-F+1)/3,1)-1,4)+1. In Excel, you can write it as =MOD(CEILING((MONTH(A1)-F+1)/3,1)-1,4)+1.
To make it easier, you can put the fiscal start month in a cell, say B1, and use =MOD(CEILING((MONTH(A1)-$B$1+1)/3,1)-1,4)+1. For a fiscal year starting in April, set B1=4.
Displaying Quarter as Text (e.g., “Q1”, “FY25-Q1”)
To show “Q1”, use ="Q"&MOD(CEILING((MONTH(A1)-F+1)/3,1)-1,4)+1. For a fiscal year label, you might want the year that the fiscal period belongs to. For example, if fiscal year starts in April, then April 2024 is FY2025 Q1. To get that, you can use =IF(MONTH(A1)>=F, YEAR(A1)+1, YEAR(A1)) for the fiscal year. Combine them: ="FY"&TEXT(IF(MONTH(A1)>=F,YEAR(A1)+1,YEAR(A1)),"0")&"-Q"&MOD(CEILING((MONTH(A1)-F+1)/3,1)-1,4)+1. This is a common need in financial reporting.
Common Errors and How to Fix Them
- Wrong quarter for boundary months: Using
=ROUNDUP(MONTH(A1)/3,0)is fine for calendar years, but if you accidentally use=ROUND(MONTH(A1)/3,0), month 2 (Feb) would round to 1, but month 3 would round to 1 as well? Actually ROUND(2/3,0)=1, ROUND(3/3,0)=1, so both Feb and Mar become Q1, but Mar should be Q1? Yes, that’s correct. The issue is with months like 4 (4/3=1.33 rounds to 1) which would be Q1, but should be Q2. So always use ROUNDUP, not ROUND. - Fiscal year formula giving 0 or 5: This happens when the formula doesn’t wrap correctly. The
MODfunction with a divisor of 4 ensures results 0-3, then +1 gives 1-4. If you forget the +1, you get 0-3. - Text concatenation errors: When building text like “Q1”, ensure you use the ampersand (&) and put quotes around the letter. Also, if the quarter number is a result of a formula, it may be stored as a number, so concatenation works fine.
- Using
WEEKNUMinstead ofMONTH: Some people try to derive quarters from week numbers, but that’s overcomplicated and error-prone because weeks don’t align cleanly with months.
Comparing with Pivot Tables and Power Query
If you have a large dataset, you might prefer to group dates in a Pivot Table. Excel’s Pivot Table has a built-in “Group” feature that can group by quarters. Select the date field, right-click, choose Group, and then select Quarters. This works for calendar quarters only. For fiscal quarters, you’d need to add a calculated column with the formula above. Power Query (Get & Transform) also has a Date.QuarterOfYear function, but again it’s calendar-based. For fiscal quarters, you’d add a custom column with a formula similar to Excel’s.
The advantage of a formula is that it’s dynamic—if you change the date, the quarter updates. Pivot tables require a refresh, and grouping definitions are static unless you use a calculated field.
Alternatives for Specific Fiscal Calendars (e.g., 4-4-5)
Some retailers use a 4-4-5 calendar (13 weeks per quarter, with months of 4,4,5 weeks). This is more complex because quarters don’t align with calendar months. In that case, you’d need a lookup table that maps each date to a fiscal period. For example, you could have a table with dates and corresponding fiscal quarters, then use VLOOKUP or XLOOKUP to find the quarter. This is beyond the scope of a simple formula, but it’s worth knowing that not all fiscal quarters are month-based.
Frequently Asked Questions
How do I get the quarter as a number without text? Use =ROUNDUP(MONTH(A1)/3,0) for calendar, or the fiscal formula above. That returns 1,2,3,4.
Can I use this formula in Google Sheets? Yes, the functions MONTH, ROUNDUP, CEILING, MOD all exist in Google Sheets with the same syntax. The only difference is that in Google Sheets, you might need to use semicolons as argument separators depending on your locale, but the formula structure is identical.
What if my dates are in text format? If A1 contains text like “2024-05-14”, Excel may not recognize it as a date. Use DATEVALUE to convert: =ROUNDUP(MONTH(DATEVALUE(A1))/3,0). But it’s better to ensure your data is stored as real dates.
Does the formula work with blank cells? If A1 is blank, MONTH(A1) returns 1 (because blank is treated as 0), so you might get Q1. To avoid that, use =IF(A1="","",ROUNDUP(MONTH(A1)/3,0)).
How do I get the fiscal year in the same formula? As shown above, use =IF(MONTH(A1)>=F, YEAR(A1)+1, YEAR(A1)) to get the fiscal year, then combine with the quarter number.
Is there a way to use LAMBDA to make a custom function? Yes, in Microsoft 365 you can define a LAMBDA like =LAMBDA(date,startMonth, MOD(CEILING((MONTH(date)-startMonth+1)/3,1)-1,4)+1)(A1,4). This allows you to reuse the logic without repeating the formula.
Related
- Excel Daily Checklist Template: How to Build One
- Excel Customer Information Template Guide
- Excel Conditional Formatting Per Row: How To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Numbering Rows: Static and DynamicTo number rows in Excel, enter =ROW()-1 in cell A2 when row 1 contains a header, then fill…
- Sort an Excel Column Alphabetically Without Mixing RowsTo sort a column alphabetically in Excel for Microsoft 365 on Windows, click a cell in the column…
- Sigma Code in Excel Solver: Build the Formula and SolveIf by “sigma code” you mean the Greek capital sigma, Σ, used to show a sum, Excel Solver…
- Excel Formula for Tax Calculation: The Complete GuideThe core Excel formula for tax calculation is =A2*B2, where A2 contains the taxable amount and B2 contains…
- Using MID in Excel: Extract Text by Position and LengthUse Excel’s MID function to return a chosen number of characters from a text value, starting at a…
- Cash Flow Formula in Excel: Examples and How to Use ItThe basic cash flow formula in Excel is cash inflows minus cash outflows. If inflows are in B2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.