Date Range Expander

Omniscope custom block · Preparation

Turn start and end dates into one row per day, week, month, quarter or year, with amounts split by days. Revenue recognition, budget phasing and occupancy in one block.

Python · pandas, numpy · nothing extra to install

The problem it solves

Contracts, subscriptions, leases, bookings, absences and projects are stored as one row with a start and an end date. But finance reports by month, operations by week and HR by day. To answer how much revenue falls into March? or how many rooms were occupied each night? every record has to be expanded into the periods it covers, with the amount split fairly.

Done by hand, this is a row-generation step plus formulas for period boundaries and proration. Date Range Expander does it in one step, for any number of records.

• 11 → 124 contracts become monthly rows
• £363k contract value booked in 2025
• £197k of it recognised within 2025
• 1 record sent to review (end before start)

How it works

• Pick the start and end date fields (real dates or text such as 31/01/2025) and the period: day, week (Monday to Sunday), month, quarter or year.
• Each output row has the period label, start and end, the days of the range inside the period, the total days and the share.
• Amount fields you choose are prorated by days, so the pieces always add back up to the original amount.
• End date inclusive (contracts) or exclusive (hotel check-out); open-ended records can run to today.
• Records that cannot be expanded go to a second output with the reason, rather than silently disappearing.

What goes in

Subscription contracts

SUB-011 has no end date (still active) and SUB-012 ends before it starts, to show both edge cases.

12 rows in the sample file.

The options

What comes out

Expanded periods

SUB-001 runs 15 January 2025 to 14 January 2026: 17 days in January, a full February, and so on, adding back up to £48,000.

Skipped records

Monthly recognised revenue from the 11 valid synthetic contracts: an Aggregate block on the expanded output, by Period.

The same output aggregated by plan for calendar 2025.

Where it pays off

SaaS and subscriptions: Revenue recognition and deferred revenue

Spread annual and multi-year contract values across months by days, then aggregate to get recognised revenue per month and the deferred balance still to come.

Hospitality and travel: Nightly occupancy

Expand bookings into one row per night (end date exclusive) to count rooms occupied, revenue per available room and peak nights.

HR and workforce: Absence and headcount by period

Expand leave and sickness records into days, or employment spells into months, for absence rates and monthly headcount that match payroll.

Property and fleet: Lease and rental phasing

Phase lease costs or vehicle hire across quarters for budgets and accruals, and see exactly how many days fall into each period.

Use cases describe typical scenarios and are illustrative.

Why do this in Omniscope

• Billing extract to monthly revenue. The block sits between the contract data and an Aggregate by Period, so the recognised-revenue chart follows each new extract, and contracts that end before they start go to a review tab in the same project.
• Splits that sum back to the contract. Amounts are prorated by days, and the tests check that the monthly pieces add back up to each contract value, as SUB-001's £48,000 does from its 17 days in January onwards.
• Proration you can change. Inclusive or exclusive end dates and open-ended records are options; the period boundaries and day shares are worked out in the block's script, if your policy splits by whole months instead.

How it fits into an Omniscope workflow

Contracts (CRM / billing) → Date Range Expander → Aggregate (by Period) → Report

Connect any data with a start and end date. The expanded output usually feeds an Aggregate block (sum the prorated amount by Period, or count records per day for occupancy) and then a Report with a line or bar chart.

Use the Skipped records output as a data-quality check: route it to a Report tab or an email output so bad dates get fixed at source.

The expansion sits between the contract data and the Aggregate, so the period report follows the billing extract, and the Skipped records output can go to a review tab in the same project.

In one step

No loop and no expression to write: one block with a period choice and a field list.

Try it

1. Open the demo project (demo.iox on the demo server, or import demo.ioz from this folder): 12 subscription contracts, one row each, with start date, end date and value.
2. The question: how much of this revenue belongs to March 2025? With one row per contract you cannot answer that in a chart.
3. Look at the block options: start, end, One row per: Month, prorate Contract Value. Nothing else to configure.
4. Run it: 124 rows, one per contract per month, with days in period and the prorated value. Look at SUB-001: 17 days in January, a full year adding back to £48,000.
5. Look at the Skipped records output: the contract whose end is before its start is flagged, not lost.
6. Finish with the monthly revenue line chart, built from the expanded output.

Running in Omniscope

The demo project's workflow: the sample data, the block, and a Validate data check on its output.

Language and libraries

Python. It uses pandas and numpy.

pandas and numpy are part of Omniscope's Docker Python image, and the block was run in both host mode and Docker mode on an Omniscope server, with the same results. It needs nothing else installed.

Tested

Run on an Omniscope server, the demo workflow finished without errors (124 records in Expanded periods and 1 in Skipped records). Automated tests also cover normal data, empty input, wrong types and large input.