Normalize Quarter Codes: 'Q1 2026', '2026-Q1', '202601' in One Formula

Netflix reports periods as 2026-Q1. Amazon uses Q1 2026. Apple’s fiscal calendar outputs 202601 (month-based). Your internal register uses 2026Q1. The reconciliation workbook joins Expected to Reported by period — and the join returns zero matches because every platform spoke a different dialect.

The Target Format

Pick one canonical format for internal use. The workable default: YYYY-Qn — 2026-Q1, 2026-Q2, 2026-Q3, 2026-Q4. It sorts lexicographically (so the quarters order correctly), is human-readable, and converts cleanly to/from everything else.

Every piece of data — whether it came from Netflix, Amazon, or your own register — gets normalized to this format before the join.

The Normalization Formula

One formula handles all four common inputs:

=IFERROR(
  IF(REGEXMATCH(A2, "^\d{4}-Q[1-4]$"), A2,
  IF(REGEXMATCH(A2, "^Q[1-4] \d{4}$"), RIGHT(A2,4)&"-"&LEFT(A2,2),
  IF(REGEXMATCH(A2, "^\d{4}Q[1-4]$"), LEFT(A2,4)&"-"&RIGHT(A2,2),
  IF(REGEXMATCH(A2, "^\d{6}$"), LEFT(A2,4)&"-Q"&CEILING(VALUE(RIGHT(A2,2))/3, 1),
  "INVALID: "&A2)))),
  "INVALID: "&A2)

Walking through the branches:

1. ^\d{4}-Q[1-4]$ — already in canonical form (e.g. 2026-Q1). Pass through.
2. ^Q[1-4] \d{4}$ — Amazon format (Q1 2026). Rearrange year-quarter.
3. ^\d{4}Q[1-4]$ — compact internal format (2026Q1). Insert the dash.
4. ^\d{6}$ — Apple month format (202601). Extract year, compute quarter from month (Jan–Mar → Q1, etc.) using CEILING(month/3, 1).
5. Anything else → INVALID: <original> so you can spot misses in review.

Drop it into a helper column next to your period column. Every row resolves to YYYY-Qn or gets flagged INVALID.

For Excel Without REGEXMATCH

If you’re on an Excel version that lacks REGEXMATCH, use LEN + position-based parsing:

=IF(LEN(A2)=7, IF(MID(A2,5,1)="-", A2, LEFT(A2,4)&"-"&RIGHT(A2,2)),
  IF(LEN(A2)=7, RIGHT(A2,4)&"-"&LEFT(A2,2),
  IF(LEN(A2)=6, LEFT(A2,4)&"-Q"&CEILING(VALUE(RIGHT(A2,2))/3, 1),
  "INVALID: "&A2)))

Verbose, but no regex dependency.

Handle Apple’s Fiscal Calendar Separately

Apple’s fiscal year doesn’t align to the calendar year — Apple Q1 ends ~late December (their fiscal year starts end of September). If you’re reporting against Apple statements, the naive CEILING(month/3, 1) gives you the wrong quarter.

Apple-specific mapping:

Apple FY  | Starts     | Ends
Q1 FY26   | 2025-09-29 | 2025-12-28
Q2 FY26   | 2025-12-29 | 2026-03-29
Q3 FY26   | 2026-03-30 | 2026-06-28
Q4 FY26   | 2026-06-29 | 2026-09-27

If you need calendar-quarter alignment to reconcile against contracts that specify calendar quarters, convert each Apple row’s underlying period (from the statement date) to the calendar quarter. Don’t use Apple’s Q label directly.

Gotchas

• Fiscal year ambiguity. Apple’s FY26 Q1 starts in September 2025. Different internal definitions of “FY26” produce different quarters. Document which system you’re aligning to.
• Two-digit year variants. Some older platforms output Q1 26 or 2601. Always expand to four-digit years first ("20"&year if you can trust the 21st-century assumption).
• Half-year and annual reports. Not every platform reports quarterly. Half-year reports show up as H1 2026 or 1H26 — different beast. Flag separately; the quarter-normalization formula won’t handle them.
• Reporting period vs. statement date. A statement issued 2026-05-15 typically covers Q1 2026 (finalized April 30 and shipped mid-May). Don’t confuse the statement’s issue date with the period it reports. The period field should read Q1 2026, not Q2 2026.

If You Need the Full Workflow

Clean Royalty Statements from Platforms into a Canonical Schema — period normalization is one step of the broader normalizer (which also handles titles, territories, and FX). Apps Script version included.

Download

The Royalty Statement Cleanup Workbook ships with the nested-IF period normalizer pre-wired into its Canonical Output sheet. Paste a raw statement, see normalized periods.