Date Range Regions

A date range region lays out a run of dates: a start date, then each next date one interval on — down a column or across a row. It is the period axis of a report built without typing a single date: twelve month starts beside a table of feeders, quarter start/end pairs above a chart's series columns, or a row of ready-made date filters that the regions below read through [Address] references.

The region is cheap to compute, so it renders live on every recalculation — there is no cache and no refresh button.

Setup

Field	Meaning
Range	Only the top-left cell places the region; the number of dates and the layout determine the extent.
Start Date	The first date. A date formula applied to the work date (`<-CY>` = first day of the year, `<-CM>` = first of the month, `<CM+1D>` = first of next month, `<-1Y-CY>` = first day of last year); a date written in the culture-independent form `2026-01-01`; `TODAY` or `WORKDATE`; or `[Address]` to take it from a cell — a `?DATE` slicer gives its date, a `?RANGE` slicer gives the start of the picked range, and `END([Address])` gives its end. A date, range or cell reference may be followed by a date formula that shifts it: `[C2]<-1Y>` is one year before the slicer, `END([C2])<-11M-CM>` the first day of the twelve months ending with it. Blank starts at the work date. Default `<-CY>`.
Interval	The date formula that steps from one date to the next: `<1M>` monthly, `<1W>` weekly, `<1Q>` quarterly, `<1Y>` yearly, `<14D>` fortnightly, `<-1M>` to run backwards. Angle brackets are optional. Blank is `<1M>`.
Number of Dates	How many dates (or periods) to lay out — at most 1000. Default 12.
Cells per Date	One Date, Two Dates or Date Range Filter — see below.
Layout	The shape the run forms. Columns lays the dates out as a column, running down the sheet (two-date periods take two columns: start, end). Rows lays them out as a row, running across (two-date periods take two rows). Default Columns. Note this reads the opposite way from table regions, where Rows means one record per row.
Include Headers	Starts the run with a header cell: Date, Start Date / End Date, or Date Filter. Off by default — a run of dates is usually a label axis beside other data.

The setup dialog shows a preview of the first and last dates the settings produce, computed from the work date. Formulas carrying [Address] references cannot be resolved off the sheet; the preview says so, and the sheet shows the result.

Cells per Date

Each step of the interval is a period boundary. With a forward interval, a period runs from its boundary up to the day before the next one: <1M> from 2026-01-01 gives 2026-01-01..2026-01-31, 2026-02-01..2026-02-28, … With a backward interval (<-1M> from <CY>, the year end) a period runs from the day after the next boundary up to its own: 2026-12-01..2026-12-31, 2026-11-01..2026-11-30, … Because the periods are derived from boundaries, month ends never drift the way a chained add one month would.

Mode	What each step writes
One Date	One date cell: the boundary itself. Formulas see a real date (`=B5 + 1`, `=Date2DMY(B5, 2)`), and a date number format on the cell re-renders it, for example as `MMM yyyy`.
Two Dates	Two adjacent date cells: the period's start and end. Feed them to `CalcDate`, to `GL.GLSum` via a formula that builds a filter, or to a chart's category range.
Date Range Filter	One text cell per period: `2026-01-01..2026-01-31` — start..end in the XML date form that every filter understands regardless of regional settings.

Driving Other Regions

The filter texts are made to be referenced. Put a date range region in Rows layout on row 2 (B2:M2), then give twelve one-row table regions below it table views like WHERE(Posting Date=FILTER([B2])), WHERE(Posting Date=FILTER([C2])), … — each column of the report is now one month, and the whole set moves when the start date changes. In formulas, reference the cell plainly: =GL.GLSum('40000..49999', B2).

Both formulas accept [Address] themselves. Point the Start Date at a ?DATE slicer — [C1] — and the user's pick moves the entire run; an interval taken from a ?LIST(<1M>;<1Q>;<1Y>)-> slicer lets the user switch between monthly, quarterly and yearly views. When rows or columns are inserted or deleted, or a template is applied elsewhere, those references shift along with the cells.

Following a Period Slicer

A dashboard's period picker is usually a ?RANGE slicer holding 2026-01-01..2026-12-31. A Start Date of [C2] takes the start of that range, so a region with Interval <1M>, Number of Dates 12 and Date Range Filter mode lays the twelve months of the picked period out as filters — and the monthly chart, its GL.GLSum cells and the per-month feeders that read those cells all move when the user picks another year. The count is fixed, so the axis never changes length; it slides.

The anchor can be shifted with a trailing date formula, which is how one slicer drives several runs:

Start Date	Run
`[C2]`	The twelve months from the picked period's start
`[C2]<-1Y>`	The same months one year earlier — the prior-year series beside the current one
`END([C2])<-11M-CM>`	The twelve months ending with the picked period (trailing twelve months)
`END([C2])<1D>`	The month after the period — a forecast strip continuing the actuals

A ?RANGE cell is text, so a formula cannot pass it to CalcDate directly. When a formula needs the range's start or end as a real date, take it from a one-date run: Start Date [C2] (or END([C2])), Number of Dates 1, One Date — the cell then holds the date and =CalcDate('<1M>', AA1) works.

Errors

The region shows an error badge instead of dates when:

the start date is neither a date, a date range nor a date formula, or the trailing formula after a date or range is not a date formula;
the start is a ?RANGE slicer whose range is open-ended on the side being used (..2026-12-31 as a start, 2026-01-01.. inside END(...));
the start is an [Address] that points at an empty cell which is not a slicer — usually the slicer sits one row up. An empty slicer is fine: the run starts at the work date until the user picks a period;
the interval is not a date formula;
the interval does not move the date — <CM> from a month end, or <CY> once the run has reached the year end — which would repeat one date forever. Step from month end to month end with <1D+CM> (first of next month, then to its end);
the number of dates is 0 or above 1000.

Examples

Twelve months of the current year, one date per row (a label column): Start <-CY>, Interval <1M>, Number of Dates 12, One Date, Columns. Set the cells' number format to MMM yyyy.

Quarter start/end pairs across the top of a report: Start <-CY>, Interval <1Q>, Number of Dates 4, Two Dates, Rows — row 2 holds the starts, row 3 the ends.

Trailing twelve months, newest first: Start <CM> (this month's end), Interval <-CM-1D> (to the previous month's end — <-1M> from a month end drifts to the 30th), Number of Dates 12, Date Range Filter, Columns.

Twelve months following the dashboard's period slicer: ?RANGE->2026-01-01..2026-12-31 in C2; Start [C2], Interval <1M>, Number of Dates 12, Date Range Filter, Columns from AB1 — then =GL.GLSum(AA30, AB1) … AB12 are the chart's monthly values and a second run with Start [C2]<-1Y> beside it is the prior-year series.

Weekly filters following a slicer: ?DATE-> in B1; Start [B1], Interval <1W>, Number of Dates 8, Date Range Filter, Rows from B2.