Why calendar quarters mislead
Take a business whose fiscal year runs from April to March. Its first quarter is April to June, and January to March is the fourth quarter of the year that began the previous April. Group a PivotTable’s dates by quarter and Excel calls January to March “Qtr1”, which puts the end of one fiscal year at the start of another, and splits every fiscal year across two calendar years.
QuickBooks Desktop’s Transaction Detail by Account export gives you a date on each transaction, which is all you need. Two formulas turn that date into a fiscal year and quarter, and the pivot does the rest.
Step 1: Pick your start month and how you name the year
The start month is the first month of your fiscal year: April if your year-end is March 31, July if it’s June 30, and so on.
Then decide how to name the year, because there’s more than one convention. This guide names a fiscal year for the calendar year it starts in, so April 2026 to March 2027 is FY2026. Some organizations name the same year for the year it ends in (FY2027), and the Government of Canada writes its April-to-March year with both (2026–27). Any of them is fine as long as you pick one and say which on the report.
Step 2: Start from a flat table with real dates
You need one row per transaction with a real date in every row. If you’re starting from a QuickBooks Desktop ledger export, this guide turns it into exactly that. The formulas below assume a table like this, with headings in row 1:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Category | Date | Account | Name | Debit | Credit | Net Amount |
If your dates are in a different column, change B2 in the formulas to match.
Step 3: Store the start month in one named cell
On a spare sheet (call it Settings), type the start month’s number in a cell: 4 for April, 7 for July, 10 for October, 1 for a calendar year. With that cell selected, click in the Name Box to the left of the formula bar, type FYStart and press Enter.
Every formula now reads the start month from that one cell, so changing your fiscal year later is a one-cell edit.
Step 4: Add Fiscal Year, Quarter and Fiscal Period columns
Add three headings after your last column and these formulas in row 2. In an Excel table they fill down by themselves; in a plain range, copy them down.
| Column | Heading | Formula (row 2) |
|---|---|---|
| H | Fiscal Year | ="FY"&YEAR(EDATE(B2,1-FYStart)) |
| I | Quarter | ="Q"&ROUNDUP(MONTH(EDATE(B2,1-FYStart))/3,0) |
| J | Fiscal Period | =H2&" "&I2 |
How it works: EDATE(B2,1-FYStart) moves each date back by one month less than the start month (three months for an April start). That shifts the first month of your fiscal year onto January, so the shifted date’s calendar year and quarter are your fiscal year and quarter. Month-end dates are safe: EDATE turns 31 May into 28 or 29 February, which is still the right month.
If you name fiscal years for the year they end in, use ="FY"&(YEAR(EDATE(B2,1-FYStart))+(FYStart>1)) for Fiscal Year instead. With a January start both conventions give the same year.
Step 5: Check the labels against known dates
With an April start (FYStart = 4), these dates should come out as shown:
| Date | Fiscal Year | Quarter | Why |
|---|---|---|---|
| 1 April 2026 | FY2026 | Q1 | First day of the year that starts in April 2026 |
| 15 January 2027 | FY2026 | Q4 | January to March is the last quarter of the year that started the April before |
| 31 March 2026 | FY2025 | Q4 | Last day of the year that started in April 2025 |
And for every start month, these are the months in each quarter, with the label a date of 15 January 2027 gets (fiscal years named for the year they start in):
| Start month (FYStart) | Q1 | Q2 | Q3 | Q4 | 15 Jan 2027 is |
|---|---|---|---|---|---|
| January (1) | Jan–Mar | Apr–Jun | Jul–Sep | Oct–Dec | FY2027 Q1 |
| February (2) | Feb–Apr | May–Jul | Aug–Oct | Nov–Jan | FY2026 Q4 |
| March (3) | Mar–May | Jun–Aug | Sep–Nov | Dec–Feb | FY2026 Q4 |
| April (4) | Apr–Jun | Jul–Sep | Oct–Dec | Jan–Mar | FY2026 Q4 |
| May (5) | May–Jul | Aug–Oct | Nov–Jan | Feb–Apr | FY2026 Q3 |
| June (6) | Jun–Aug | Sep–Nov | Dec–Feb | Mar–May | FY2026 Q3 |
| July (7) | Jul–Sep | Oct–Dec | Jan–Mar | Apr–Jun | FY2026 Q3 |
| August (8) | Aug–Oct | Nov–Jan | Feb–Apr | May–Jul | FY2026 Q2 |
| September (9) | Sep–Nov | Dec–Feb | Mar–May | Jun–Aug | FY2026 Q2 |
| October (10) | Oct–Dec | Jan–Mar | Apr–Jun | Jul–Sep | FY2026 Q2 |
| November (11) | Nov–Jan | Feb–Apr | May–Jul | Aug–Oct | FY2026 Q1 |
| December (12) | Dec–Feb | Mar–May | Jun–Aug | Sep–Nov | FY2026 Q1 |
Step 6: Build the fiscal quarter PivotTable
- Select a cell in the table and choose Insert › PivotTable.
- Put Category and then Account in Rows.
- Put Fiscal Period in Columns. Labels such as
FY2026 Q1sort in date order as plain text, so there’s nothing to group. Don’t drag Date into Columns: Excel will offer to group it, and its quarters are calendar quarters. - Put Net Amount in Values and make sure it says Sum.
- Put Fiscal Year in Filters to show one year at a time.
With Net Amount as credit less debit, income shows as positive and expenses as negative. A general ledger also contains balance sheet accounts, so filter Category to your income and expense accounts if you want a profit and loss view by quarter.
The shortcut
LedgerCleaner, our Windows 10/11 app for QuickBooks Desktop exports, has this built in. Pick the month your fiscal year starts and every row gets a Fiscal Year, Quarter and Fiscal Period, and the workbook includes a Quarters PivotTable with FY2026 Q1, FY2026 Q2 and so on across the top. The app remembers the month, records it on the workbook’s Sources sheet, and keeps an existing workbook’s fiscal year when you add files to it. Changing the month and saving again relabels the whole workbook at once, so the sheets never disagree. It doesn’t read QuickBooks Online exports, and the pivots need desktop Excel for Windows.
Questions
What if we change our fiscal year-end?
Change the number in the FYStart cell. Every Fiscal Year, Quarter and Fiscal Period label recalculates, and Data › Refresh All updates the pivot. Label the report clearly, because the same dates now fall into different quarters than before.
Our year doesn’t start on the 1st of a month. Do these formulas work?
No. They assume the fiscal year starts on the first day of a month. For a 52–53 week year or a 4-4-5 calendar, make a small table of each period’s start date and look each transaction’s date up in it instead, with XLOOKUP using match mode -1 (exact match or next smaller item), or VLOOKUP with TRUE as its last argument in older versions of Excel.
Can I use these formulas for a calendar year too?
Yes. Set FYStart to 1 and you get ordinary calendar years and quarters, with the same labels and the same pivot.