Date Column Detection
This article explains how Model Reef detects and interprets date and period columns when you import data from PDF or Excel.
You will learn:
How period columns are identified.
How frequency and alignment are inferred.
How to override or correct detected dates.
How date detection interacts with the model timeline.
What counts as a date or period column
During import, Model Reef looks for columns that represent time, such as:
Single years:
2022,2023,2024.Financial years:
FY22,FY23,2024F.Calendar dates:
31 Dec 2023,Mar-24.Quarters:
Q1 2024,Q2 2025.Months:
Jan 24,Feb 24,Mar 24.
These columns become the period axis for the imported series.
Columns that do not look like dates are treated as metric or metadata columns and usually become the leftmost row labels or attributes.
How frequency is inferred
Model Reef attempts to infer the frequency of each table or series by analysing the pattern of period labels, for example:
Differences of 1 year between periods indicate annual data.
Quarters within a given year indicate quarterly data.
Sequences of months indicate monthly data.
If there is only a single period column, the importer may default to annual frequency, which you can change manually if needed.
The chosen frequency affects how imported data is aligned to the model timeline and how it is resampled if the model uses a different base frequency.
Aligning imported periods to the model
When the importer knows the period labels and frequency, it maps them onto the model timeline. This mapping depends on:
The model start date.
The model base frequency (monthly, quarterly, yearly).
The interpretation of the period label (for example whether
2024means calendar year or financial year).
You may need to confirm or adjust:
The year end month (for example December for calendar year or June for some financial years).
Whether a label like
2024should align to FY24 or CY24 in your context.How partial periods should be handled if the model starts mid year.
Once aligned, the imported series will occupy specific periods in the model’s time grid.
Overriding detected dates
If Model Reef misidentifies a column or misinterprets the period, you can usually:
Mark or unmark a column as a date column.
Change the frequency (for example from annual to quarterly).
Adjust the interpretation of ambiguous labels (for example mapping
FY23to a chosen year range).
It is better to correct date detection at import time rather than trying to fix misaligned data later.
Handling partial histories and forecasts
Many tables contain:
Historical actuals up to a certain date.
Forecast or budget values for future periods.
Mixed labelling such as
2022A,2023A,2024F,2025F.
Model Reef imports the entire series and uses your later modelling choices to decide where historicals end and forecasts begin.
Typical patterns:
Set the model start date so that some periods contain imported history only.
Use variables and drivers to generate forward values beyond the imported periods.
Compare historical and forecast segments in charts and dashboards.
The date detection step focuses on getting all periods into the correct place on the timeline. How you split history vs forecast is a separate modelling choice.
Interaction with model base frequency
If the imported data frequency differs from the model base frequency, Model Reef will resample:
Upwards (for example monthly model from annual data): annual values are spread or allocated across months.
Downwards (for example annual reports from quarterly models): subtotals are aggregated into annual levels for comparison.
For most imports, the important point is that you get values anchored on the right year, quarter or month. Fine grained resampling decisions can be adjusted later by changing variables or drivers.
Related articles
Last updated