Checking Date Ranges and Gaps in Time Series Data

You have a year of transaction data. Or so you were told. But does the file actually cover January through December? Are there missing weeks? Did something go wrong in March, where the record count collapses to a third of every other month?

These aren't hypothetical problems. Missing date ranges are one of the most common and most damaging data issues because they're invisible. Your totals look fine. Your averages look reasonable. But you're making decisions based on 10 months of data thinking it's 12.

See the time series at a glance

Click any date column in ExploreMyData to open the Date Column Explorer. It shows a time-series chart: a count of records per time period across the full range of your data.

The default view uses month granularity, which is the right starting point for most datasets. You'll immediately see the shape of your data over time. A healthy dataset shows a relatively consistent pattern. Gaps show up as dips to zero. Partial months at the start or end show up as shorter bars.

Check the boundaries first

Before worrying about gaps in the middle, confirm the start and end dates match your expectations. The chart's x-axis shows you the full range. If someone told you this is "2025 data" but the chart starts in March 2025 and ends in November 2025, you're missing four months.

This is surprisingly common. Data exports often have implicit date filters that nobody mentions. Or the source system didn't go live until March, so those first two months simply don't exist. Either way, you need to know this before doing year-over-year comparisons or calculating annual totals.

Change granularity to find different patterns

The Date Column Explorer has five granularity buttons: day, week, month, quarter and year. Each level reveals different problems.

Monthly is the default and the right place to start. It shows the big picture, seasonal shape, and any month that's obviously light.

Weekly shows medium-scale gaps. A system outage that lasted ten days barely dents a monthly bar, but at weekly granularity it's two empty columns you can't miss. That's exactly what happened to March above.

Daily reveals the fine detail. Weekday against weekend patterns in retail data. Holidays where nothing was recorded. The specific days where a gap starts and ends.

Quarterly is the one people forget, and it's the right lens when your business reports on quarters. A month that's 30% light looks alarming on its own and often disappears once you see the quarter came in normal, because the orders simply landed late. It also makes Q-over-Q comparisons a glance rather than a calculation.

Yearly is for multi-year datasets. With five years loaded, yearly granularity tells you whether each year has roughly the same volume or whether there's a trend underneath everything else you're about to measure.

Extract date parts to quantify gaps

The chart shows you gaps visually. To quantify them, use Extract Date Part to pull month and year into their own columns. Then you can group by month/year and count records per period.

For example, extract the month from an "order_date" column to create an "order_month" column, then run Group & Aggregate on it with COUNT. Now you have exact counts: January 4,201, February 4,089, March 1,268, April 3,977. That short March bar is a concrete number you can put in an email.

It also gives you an estimate of the damage. February is 4,089 and April is 3,977, so March should have been somewhere near 4,000. It came in 2,700 short. That's the sentence someone else needs to hear, not "there's a dip in the chart".

One caveat on Extract Date Part: it returns the number, so March is 3, not "2025-03". If your data spans multiple years, extract the year into its own column too and group on both, otherwise every March in the file collapses into one bucket.

Use filters to investigate suspicious periods

When you spot a gap or an unusual spike, filter the data to just that time period. On a date column the Filter operators are is, before, after, on or before, on or after, is Empty and is NOT Empty, so a range is two conditions joined with AND: "on or after" the start, "on or before" the end. Faster still, drag-select the suspect bars in the Date Column Explorer and it builds the filter for you.

Say the chart shows a spike in December that's 3x the normal volume. Filter to December and look at the data. Are there duplicate records? Was there a bulk import? Did a different system's data get merged in? The rows around the anomaly usually contain the explanation.

For gaps, filter to the week just before and after the missing period. Look at the last records before the gap and the first records after. Sometimes you'll see a change in format, source, or ID sequence that explains what happened.

Measure gaps between consecutive records

For data that should arrive at regular intervals (daily reports, hourly sensor readings), you want the gap between each row and the one before it. Date Difference alone won't give you that: it compares two date columns on the same row, and "the previous row" isn't a column yet. So make it one. This is two steps.

Step 1: Window Function. Pick LAG as the function and order_date as the column. Set Order by to order_date as well, and here's the bit that matters: a direction switch appears once you set Order by, and it defaults to Largest first. Flip it to Smallest first, or "previous" means the next-newest row and every gap comes out negative. Name the output prev_order_date:

LAG(order_date) OVER (ORDER BY order_date ASC) AS prev_order_date

Step 2: Date Difference. First date column prev_order_date, second date column order_date, unit day, output name days_since_prev:

DATEDIFF('day', prev_order_date, order_date) AS days_since_prev

Order matters in that pair too. Earlier date first, later date second, or you get negatives again. The very first row has no previous row, so its prev_order_date is NULL and days_since_prev comes out NULL. That's correct, and it's one row you can ignore.

In a daily dataset most values should be 1. To surface the worst offenders, either click the days_since_prev header in the grid to sort descending, or add Top / Bottom Rows with mode "top", sort by days_since_prev, count 20. A 3-day gap is probably a weekend. A 15-day gap is the March outage: last order on the 2nd, next one on the 17th.

One prerequisite: LAG has to order by a real date column. If order_date is still text, the ordering is alphabetical and the whole exercise is nonsense. Check the badge in the column header reads D before you start.

Common date range problems

After checking enough datasets, certain patterns keep appearing:

• Partial first/last month: Data starts on the 15th or ends on the 3rd. Whoever pulled the export used the wrong date range.
• Holiday gaps: No records during the last week of December. Either the business was closed or the recording system was paused.
• System migration gaps: A gap of a few days or weeks followed by a change in data patterns (new columns, different ID formats). The old system was turned off before the new one was fully running.
• Timezone issues: Records that appear to be missing on the 1st of each month because timestamps were stored in UTC but the export filtered by local time.
• Future dates: Records dated next year or in 2099. Usually placeholder dates for "no end date" or data entry errors.

Check your dates before you analyze

The date check takes two minutes. Open the Date Column Explorer, confirm the range, scan for gaps, switch to weekly granularity to catch smaller holes. That's it.

Skipping this step means you might calculate a "monthly average" that divides by 12 when you only have 10 months of data. Or build a year-over-year comparison where one year is missing Q3. The numbers will look plausible, which is exactly what makes date gaps dangerous.

Check your date ranges →